如何获取 SQL Server 表中的第 n 行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2273558/
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
How do I get the nth row in a SQL Server table?
提问by Kip Birgen
How would you get the nth row (i.e 5th row) from the result of a query in SQL Server?
如何从 SQL Server 中的查询结果中获取第 n 行（即第 5 行）？
回答by JoshBerke
SQL Server 2005 and newer:
SQL Server 2005 及更新版本：
with Records AS(select row_number() over(order by datecreated) as 'row', *
from Table)
select * from records
where row=5
You can change the order by to determine how you sort the data to get the fifth row.
您可以通过更改顺序来确定如何对数据进行排序以获得第五行。
Tested on my local install: Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 (Build 7600: )
在我的本地安装上测试：Microsoft SQL Server 2005 - 9.00.4053.00 (X64) 2009 年 5 月 26 日 14:13:01 版权所有 (c) 1988-2005 Microsoft Corporation Developer Edition（64 位），Windows NT 6.1（内部版本 7600：）
回答by Paul Alan Taylor
As of 2005, the ROW_NUMBER() function is available.
截至 2005 年，ROW_NUMBER() 函数可用。
SELECT * FROM
(
SELECT r.*, ROW_NUMBER() OVER (ORDER BY SomeField ASC) AS RowNum
) sorty
WHERE RowNum = 5
回答by mwigdahl
You have many choices, based on this link. All these techniques apply to SQL 2000. The one I've used before is option #1, where you take a 5-element subset ordered one way and then pick the "TOP" row out of the subset ordered the other way. Works great!
基于此链接，您有很多选择。所有这些技术都适用于 SQL 2000。我之前使用过的方法是选项 #1，在该选项中，您采用一种方式排序的 5 元素子集，然后从以另一种方式排序的子集中选取“TOP”行。效果很好！
SELECT TOP 1 FName
FROM
(
SELECT TOP 5 FName
FROM Names
ORDER BY FName
) sub
ORDER BY FName DESC
In SQL 2005 and up it's easier -- you have row ordering functions such as ROW_NUMBER()which will do what you need directly.
在 SQL 2005 及更高版本中，它更容易——您有行排序函数，例如ROW_NUMBER()，它可以直接执行您需要的操作。
回答by Manas Sahu
If you are using SQL Serever 2005 or above you can use CTE functionality.
如果您使用的是 SQL Server 2005 或更高版本，则可以使用 CTE 功能。
Otherwise you can use this simple query
否则你可以使用这个简单的查询
SELECT TOP 1 EmpID
FROM (SELECT TOP 5 EmpID FROM EmpMaster ORDER BY Salary) T
ORDER BY Emp DESC
回答by Rajesh Ramabathiran
You can make use of Common Table Expression (CTE) and Window Function, ROW_NUMBER() (compatability from SQL 2005) to solver your problem.
您可以使用通用表表达式 (CTE) 和窗口函数 ROW_NUMBER()（与 SQL 2005 兼容）来解决您的问题。
Let us assume that the table name is tableOne and it has three columns (col1, col2, col3)
让我们假设表名是 tableOne，它有三列（col1、col2、col3）
To make the definition complete, you need to specify the order with respect to which you want the rows numbered. The following scripts should help you get the nth row of the table.
为了使定义完整，您需要指定行编号的顺序。以下脚本应该可以帮助您获取表的第 n 行。
--table definition
create table tableOne
(
col1 varchar(10)
,col2 varchar(10)
,col3 varchar(10)
)
go
-- sample data creation
insert into tableOne VALUES ('c11','c12','c13')
insert into tableOne VALUES ('c21','c22','c23')
insert into tableOne VALUES ('c31','c32','c33')
insert into tableOne VALUES ('c41','c42','c43')
insert into tableOne VALUES ('c51','c52','c53')
insert into tableOne VALUES ('c61','c62','c63')
go
-- obtaining nth row using CTE and Window Function
WITH NthRowCTE AS
(
SELECT
ROW_NUMBER() OVER (ORDER BY col1) AS RNum
, *
FROM tableOne
)
SELECT * FROM NthRowCTE WHERE RNum = 5
GO
WITH CTE and Window Function, you have the complete flexibility to output data the way you want.
使用 CTE 和窗口函数，您可以完全灵活地以您想要的方式输出数据。
回答by Hyman.mike.info
SIMPLE
简单的
-- return first record
-- 返回第一条记录
select * from titles a where 1 = (select count(title_id) from titles b where a.title_id >= b.title_id)
select * from titles a where 1 = (select count(title_id) from titles b where a.title_id >= b.title_id)
-- return second record and so onselect * from titles a where 2 = (select count(title_id) from titles b where a.title_id >= b.title_id)
-- 返回第二条记录，依此类推select * from titles a where 2 = (select count(title_id) from titles b where a.title_id >= b.title_id)
回答by Mark Schultheiss
A BIT more/different than you asked for, but here is some stuff: Say you have a need to find the nth row of one table, on a specific datetime sequence and insert a value from it into another table if its unique identifier is not already in the other table. This gets the value from that first tables nth row. myuniquecol is an identifer/col that identifies the group of rows that all have the same value in the first table which have unique mydatetime values
比您要求的更多/不同，但这里有一些东西：假设您需要在特定日期时间序列上找到一个表的第 n 行，如果唯一标识符不是，则将其中的值插入另一个表中已经在另一张桌子上了。这从第 n 行的第一个表中获取值。myuniquecol 是一个标识符/列，用于标识在第一个表中都具有相同值的行组，这些行具有唯一的 mydatetime 值
( SELECT TOP 1 mycol FROM
( SELECT TOP 5 * FROM mytable
WHERE mytable.myuniquecol NOT IN (select myuniquecol from myothertable)
AND mytable.myuniquecol = myuniquecol
ORDER BY myuniquecol asc, mydatetime desc
) AS me
ORDER BY myuniquecol desc, mydatetime asc
) AS mycolnew
This is useful to find the nth row in one table, and insert it in as the nth col in another table.
这对于在一个表中查找第 n 行并将其作为另一个表中的第 n 列插入很有用。
table1: myuniquecol, mycol, mydatetime
表 1：myuniquecol、mycol、mydatetime
table2: myuniquecol, mycol1, mycol2, mycol3...
表 2：myuniquecol、mycol1、mycol2、mycol3...
should work in any SQL :)
应该适用于任何 SQL :)
NOTE: mytable has a column called mycol
注意：mytable 有一列名为 mycol
EDIT: took out some stuff because of SQL SERVER limitation on TOP
编辑：由于 TOP 上的 SQL SERVER 限制，取出了一些东西
