我感兴趣的是从数据库表中选择第n行的一些(理想的)数据库不可知的方法。看看如何使用以下数据库的本机功能来实现这一点也很有趣:

SQL Server MySQL PostgreSQL SQLite 甲骨文

我目前正在SQL Server 2005中做以下事情,但我有兴趣看到其他更不可知论的方法:

WITH Ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNumber, OrderID, OrderDate
FROM Orders)
SELECT *
FROM Ordered
WHERE RowNumber = 1000000

以上SQL: Firoz Ansari的Weblog的功劳

更新:参见Troels Arvin关于SQL标准的回答。Troels,你有我们可以引用的链接吗?


当前回答

在我看来,为了提高效率,您需要1)生成一个小于数据库记录数量的0到1之间的随机数,2)能够选择该位置的行。不幸的是,不同的数据库有不同的随机数生成器,以及在结果集中的某个位置选择一行的不同方法——通常您指定要跳过多少行和需要多少行,但不同的数据库有不同的做法。下面是在SQLite中为我工作的一些东西:

select * 
from Table 
limit abs(random()) % (select count(*) from Words), 1;

It does depend on being able to use a subquery in the limit clause (which in SQLite is LIMIT <recs to skip>,<recs to take>) Selecting the number of records in a table should be particularly efficient, being part of the database's meta data, but that depends on the database's implementation. Also, I don't know if the query will actually build the result set before retrieving the Nth record, but I would hope that it doesn't need to. Note that I'm not specifying an "order by" clause. It might be better to "order by" something like the primary key, which will have an index - getting the Nth record from an index might be faster if the database can't get the Nth record from the database itself without building the result set.

其他回答

下面是我最近为Oracle编写的一个sproc的通用版本,它允许动态分页/排序——HTH

-- p_LowerBound = first row # in the returned set; if second page of 10 rows,
--                this would be 11 (-1 for unbounded/not set)
-- p_UpperBound = last row # in the returned set; if second page of 10 rows,
--                this would be 20 (-1 for unbounded/not set)

OPEN o_Cursor FOR
SELECT * FROM (
SELECT
    Column1,
    Column2
    rownum AS rn
FROM
(
    SELECT
        tbl.Column1,
        tbl.column2
    FROM MyTable tbl
    WHERE
        tbl.Column1 = p_PKParam OR
        tbl.Column1 = -1
    ORDER BY
        DECODE(p_sortOrder, 'A', DECODE(p_sortColumn, 1, Column1, 'X'),'X'),
        DECODE(p_sortOrder, 'D', DECODE(p_sortColumn, 1, Column1, 'X'),'X') DESC,
        DECODE(p_sortOrder, 'A', DECODE(p_sortColumn, 2, Column2, sysdate),sysdate),
        DECODE(p_sortOrder, 'D', DECODE(p_sortColumn, 2, Column2, sysdate),sysdate) DESC
))
WHERE
    (rn >= p_lowerBound OR p_lowerBound = -1) AND
    (rn <= p_upperBound OR p_upperBound = -1);

当我们在MSSQL 2000中工作时,我们做了所谓的“三重翻转”:

编辑

DECLARE @InnerPageSize int
DECLARE @OuterPageSize int
DECLARE @Count int

SELECT @Count = COUNT(<column>) FROM <TABLE>
SET @InnerPageSize = @PageNum * @PageSize
SET @OuterPageSize = @Count - ((@PageNum - 1) * @PageSize)

IF (@OuterPageSize < 0)
    SET @OuterPageSize = 0
ELSE IF (@OuterPageSize > @PageSize)
    SET @OuterPageSize = @PageSize

DECLARE @sql NVARCHAR(8000)

SET @sql = 'SELECT * FROM
(
    SELECT TOP ' + CAST(@OuterPageSize AS nvarchar(5)) + ' * FROM
    (
        SELECT TOP ' + CAST(@InnerPageSize AS nvarchar(5)) + ' * FROM <TABLE> ORDER BY <column> ASC
    ) AS t1 ORDER BY <column> DESC
) AS t2 ORDER BY <column> ASC'

PRINT @sql
EXECUTE sp_executesql @sql

它并不优雅,速度也不快,但它奏效了。

没有什么花哨的,没有特殊的功能,以防你像我一样使用Caché……

SELECT TOP 1 * FROM (
  SELECT TOP n * FROM <table>
  ORDER BY ID Desc
)
ORDER BY ID ASC

假设您有一个可以信任的ID列或日期戳列。

我怀疑这是一种非常低效的方法,但这是一种相当简单的方法,它在我尝试的一个小型数据集上有效。

select top 1 field
from table
where field in (select top 5 field from table order by field asc)
order by field desc

这将得到第5项,改变上面的第二个数字来得到不同的第n项

仅限SQL服务器(我认为),但应该适用于不支持ROW_NUMBER()的旧版本。

SQL 2005及以上版本内置了这个特性。使用ROW_NUMBER()函数。它非常适合浏览<< Prev和Next >>样式的网页:

语法:

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
            *
        FROM
            Table_1
    ) sub
WHERE
    RowNum = 23