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


当前回答

select * from 
(select * from ordered order by order_id limit 100) x order by 
x.order_id desc limit 1;

首先按升序选择前100行,然后按降序选择最后一行并限制为1行。然而,这是一个非常昂贵的语句,因为它访问了两次数据。

其他回答

难以置信,你能找到一个SQL引擎执行这个…

WITH sentence AS
(SELECT 
    stuff,
    row = ROW_NUMBER() OVER (ORDER BY Id)
FROM 
    SentenceType
    )
SELECT
    sen.stuff
FROM sentence sen
WHERE sen.row = (ABS(CHECKSUM(NEWID())) % 100) + 1

PostgreSQL支持SQL标准定义的窗口函数,但它们很笨拙,所以大多数人使用(非标准)LIMIT / OFFSET:

SELECT
    *
FROM
    mytable
ORDER BY
    somefield
LIMIT 1 OFFSET 20;

这个例子选择了第21行。OFFSET 20告诉Postgres跳过前20条记录。如果您没有指定ORDER BY子句,则无法保证您将返回哪条记录,这很少有用。

SQL SERVER


从顶部选择第n条记录

SELECT * FROM (
SELECT 
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID) AS ROW
FROM TABLE 
) AS TMP 
WHERE ROW = n

从底部选择第n条记录

SELECT * FROM (
SELECT 
ID, NAME, ROW_NUMBER() OVER(ORDER BY ID DESC) AS ROW
FROM TABLE 
) AS TMP 
WHERE ROW = n

例如,如果你想在MSSQL中选择每10行,你可以使用;

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY ColumnName1 ASC) AS rownumber, ColumnName1, ColumnName2
  FROM TableName
) AS foo
WHERE rownumber % 10 = 0

只要打开MOD,把10号换成任何你想要的数字。

从表中选择N个记录号

select * from
 (select row_number() over (order by Rand() desc) as Rno,* from TableName) T where T.Rno = RecordNumber

Where  RecordNumber --> Record Number to Select
       TableName --> To be Replaced with your Table Name

例如,要从表Employee中选择第5条记录,您的查询应该是

select * from
 (select row_number() over (order by Rand() desc) as Rno,* from Employee) T where T.Rno = 5