我感兴趣的是从数据库表中选择第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,你有我们可以引用的链接吗?


当前回答

对于SQL Server,一般的方法是通过行号:

SET ROWCOUNT @row --@row = the row number you wish to work on.

例如:

set rowcount 20   --sets row to 20th row

select meat, cheese from dbo.sandwich --select columns from table at 20th row

set rowcount 0   --sets rowcount back to all rows

这将返回第20行的信息。一定要在后面输入行数0。

其他回答

Oracle:

select * from (select foo from bar order by foo) where ROWNUM = x
SELECT * FROM emp a
WHERE  n = ( 
    SELECT COUNT( _rowid)
    FROM emp b
    WHERE a. _rowid >= b. _rowid
);

在标准的可选部分中有一些方法可以做到这一点,但许多数据库都支持自己的方法。

一个非常好的网站是http://troels.arvin.dk/db/rdbms/#select-limit。

基本上,PostgreSQL和MySQL支持非标准:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2和MSSQL支持标准窗口函数:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(我只是从上面链接的网站复制,因为我从不使用这些数据库)

更新:从PostgreSQL 8.4开始支持标准窗口函数,因此希望第二个示例也适用于PostgreSQL。

更新:SQLite在2018-09-15版本3.25.0中添加了窗口函数支持,因此这两种表单也可以在SQLite中工作。

这里有一个快速解决你困惑的方法。

SELECT * FROM table ORDER BY `id` DESC LIMIT N, 1

这里你可以通过填充N=0得到最后一行,通过填充N=1得到倒数第二行,通过填充N=3得到倒数第四行,等等。

这是面试中很常见的问题,也是一个很简单的回答。

如果你想要数量,ID或一些数字排序顺序,你可以在MySQL中使用CAST函数。

SELECT DISTINCT (`amount`) 
FROM cart 
ORDER BY CAST( `amount` AS SIGNED ) DESC 
LIMIT 4 , 1

在这里,通过填写N = 4,您将能够从CART表中获得第五次最高金额记录。您可以匹配字段和表名并提出解决方案。

当我们在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

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