在 SQL 查询中选择第 N 条记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1022514/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting Nth Record in an SQL Query
提问by Hyman Mills
I have an SQL Query that i'm running but I only want to select a specific row. For example lets say my query was:
我有一个正在运行的 SQL 查询，但我只想选择特定的行。例如，假设我的查询是：
Select * from Comments
Lets say this returns 10 rows, I only want to select the 8th record returned by this query. I know I can do:
假设这返回 10 行，我只想选择此查询返回的第 8 条记录。我知道我可以做到：
Select Top 5 * from Comments
To get the top 5 records of that query but I only want to select a certain record, is there anything I can put into this query to do that (similar to top).
要获取该查询的前 5 条记录，但我只想选择某条记录，是否可以在此查询中放入任何内容（类似于 top）。
Thanks
谢谢
Hyman
Hyman
回答by Johnno Nolan
This is a classic interview question.
这是一道经典的面试题。
In Ms SQL 2005+ you can use the ROW_NUMBER()keyword and have the Predicate ROW_NUMBER = n
在 Ms SQL 2005+ 中，您可以使用ROW_NUMBER()关键字并使谓词 ROW_NUMBER = n
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber = 5;
In SQL2000 you could do something like
在 SQL2000 中你可以做类似的事情
SELECT Top 1 *FROM
[tblApplications]
where [ApplicationID] In
(
SELECT TOP 5 [ApplicationID]
FROM [dbo].[tblApplications]
order by applicationId Desc
)
回答by Degan
How about
怎么样
SELECT TOP 1 * FROM
(SELECT TOP 8 * FROM Comments ORDER BY foo ASC)
ORDER BY foo DESC
回答by John Saunders
First, you should say which RDBMS you're using.
首先，您应该说明您使用的是哪个 RDBMS。
Second, you should give careful thought to what it is you're trying to accomplish. Relational Databases are set-based. In general, the order of elements in a set does not matter. You'll want to ask why it matters in this case, then see if there's a better way to embed the concept of order into the query itself.
其次，你应该仔细考虑你想要实现的目标。关系数据库是基于集合的。通常，集合中元素的顺序无关紧要。您会想问为什么在这种情况下它很重要，然后看看是否有更好的方法将顺序的概念嵌入到查询本身中。
For instance, in SQL Server 2005 (and other RDBMS), you can use the ROW_NUMBER function to assign a sequential number to each row returned, based on the criteria you specify. You could then select rows based on the row number. Example from Books Online:
例如，在 SQL Server 2005（和其他 RDBMS）中，您可以使用 ROW_NUMBER 函数根据您指定的条件为返回的每一行分配一个序列号。然后，您可以根据行号选择行。来自在线书籍的示例：
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
回答by Ben Hughes
SELECT * FROM comments WHERE ...conditions... LIMIT 1 OFFSET 8
OFFSET is a good thing for MySQL
OFFSET 对 MySQL 来说是个好东西
回答by rein
For SQL Server 2005:
对于 SQL Server 2005：
select rank() OVER (ORDER BY c.subject, c.date) as rank, c.subject, c.date
from comments c
where rank = 8
回答by shahkalpesh
I have read the question & your comments on you would want next 3 blog comments etc.
我已经阅读了这个问题，你对你的评论想要接下来的 3 个博客评论等。
How is your tables structured?
Assume that you have blog post Id & comment Id is generated in ascending order for each blog post, you could do a SELECT based on the current Id.
你的表是如何结构的？
假设您有博客文章 ID 和评论 ID 是按每个博客文章的升序生成的，您可以根据当前 ID 执行 SELECT。
e.g. if the blogpostId = 101, you get the top 3 comments order by posted Id. Now lets say, you want to get the next 3 comments - you could do a SELECT WHERE commentId between the last comment id shown TO the comment id - 3
例如，如果 blogpostId = 101，您将按已发布的 ID 获得前 3 条评论顺序。现在让我们说，您想要获得接下来的 3 条评论 - 您可以在显示到评论 ID 的最后一条评论 ID 之间执行 SELECT WHERE commentId - 3
But all that depends on how your tables are defined.
但这一切都取决于您的表是如何定义的。
回答by AlejandroR
In SQL 2000 where you do not have ROW_NUMBER() function you could use a work-around like this:
在没有 ROW_NUMBER() 函数的 SQL 2000 中，您可以使用如下解决方法：
SELECT CommentsTableFieldList, IDENTITY(INT, 1,1) as seqNo
INTO #SeqComments
FROM Comments
SELECT * FROM #SeqComments
WHERE seqNo = 8
回答by Harendra Negi
select top 1 *
from TableName
where ColumnName1 in
(
select top nth ColumnName1
from TableName
order by ColumnName1 desc
)
order by ColumnName1 desc
回答by Paulo Santos
Well, in T-SQL (the dialect for SQL Server) you can do the following:
好吧，在 T-SQL（SQL Server 的方言）中，您可以执行以下操作：
SELECT TOP 1 *
FROM (SELECT TOP 8 *
FROM Table
ORDER
BY SortField)
ORDER
BY SortField DESC
This way you get the 8th record.
这样你就得到了第 8 条记录。
回答by Ben S
From the SELECT reference, use the LIMIT keyword:
在SELECT 参考中，使用 LIMIT 关键字：
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows
Note: this is for MySQL, other SQL engines may have a different keyword.
注意：这是针对 MySQL 的，其他 SQL 引擎可能有不同的关键字。
