我感兴趣的是从数据库表中选择第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引擎执行这个…
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
其他回答
下面是我最近为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);
在我看来，为了提高效率，您需要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.
与一些答案所声称的相反，SQL标准在这个主题上并不是沉默的。
自SQL:2003以来，您已经能够使用“窗口函数”来跳过行和限制结果集。
在SQL:2008中，添加了一个稍微简单的方法，使用 偏移跳过行 只取前n行
就我个人而言，我不认为SQL:2008的加入是真的有必要的，所以如果我是ISO，我会把它排除在一个已经相当大的标准之外。
SELECT
top 1 *
FROM
table_name
WHERE
column_name IN (
SELECT
top N column_name
FROM
TABLE
ORDER BY
column_name
)
ORDER BY
column_name DESC
我写这个查询是为了求第n行。 使用此查询的示例如下
SELECT
top 1 *
FROM
Employee
WHERE
emp_id IN (
SELECT
top 7 emp_id
FROM
Employee
ORDER BY
emp_id
)
ORDER BY
emp_id DESC
对于SQL server，下面的语句将返回给定表的第一行。
declare @rowNumber int = 1;
select TOP(@rowNumber) * from [dbo].[someTable];
EXCEPT
select TOP(@rowNumber - 1) * from [dbo].[someTable];
你可以这样循环遍历这些值:
WHILE @constVar > 0
BEGIN
declare @rowNumber int = @consVar;
select TOP(@rowNumber) * from [dbo].[someTable];
EXCEPT
select TOP(@rowNumber - 1) * from [dbo].[someTable];
SET @constVar = @constVar - 1;
END;
