如何在SQL数据库表中选择第n行?

我感兴趣的是从数据库表中选择nth行的一些(理想的)数据库不可知的方法。看看如何使用以下数据库的本机功能来实现这一点也很有趣:

  • 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的博客

更新:关于SQL标准请参见Troels Arvin的答案。Troels，你有我们可以引用的链接吗?

988942 次浏览
小开

添加:

LIMIT n,1

这将把结果限制为从结果n开始的一个结果。

小开

LIMIT n,1不能在MS SQL Server中工作。我认为它是唯一一个不支持这种语法的主流数据库。公平地说，它不是SQL标准的一部分，尽管它得到了如此广泛的支持，它应该是SQL标准的一部分。在一切除了SQL服务器限制工作得很好。对于SQL server，我还没有找到一个优雅的解决方案。

小开

下面是我最近为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);
小开

我不确定其他的，但我知道SQLite和MySQL没有任何“默认”。行排序。至少在这两种方言中，下面的代码片段从the_table中抓取了第15个条目，按添加的日期/时间排序:

SELECT *
FROM the_table
ORDER BY added DESC
LIMIT 1,15

(当然，您需要添加一个DATETIME字段，并将其设置为条目添加的日期/时间…)

小开

我怀疑这是一种非常低效的方法，但这是一种相当简单的方法，它在我尝试的一个小型数据集上有效。

select top 1 field
from table
where field in (select top 5 field from table order by field asc)
order by field desc

这将得到第5项，改变上面的第二个数字来得到不同的第n项

仅限SQL服务器(我认为)，但应该适用于不支持ROW_NUMBER()的旧版本。

小开

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

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

这个例子选择了第21行。OFFSET 20告诉Postgres跳过前20条记录。如果你没有指定ORDER BY子句，就不能保证你会得到哪条记录，这很少有用。

小开

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

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

小开

Oracle:

select * from (select foo from bar order by foo) where ROWNUM = x
小开

但说真的，所有这些不都是好的数据库设计的小把戏吗?有几次我需要这样的功能，它是为了一个简单的一次性查询，以快速生成报告。对于任何实际工作，使用这样的技巧都是在招惹麻烦。如果需要选择一个特定的行，那么只要有一个具有连续值的列就可以了。

小开

在Sybase SQL Anywhere:

SELECT TOP 1 START AT n * from table ORDER BY whatever

别忘了ORDER BY，否则毫无意义。

小开
最佳答案

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

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中工作。

小开

1个小变化，n-1而不是n。

select *
from thetable
limit n-1, 1
小开

与一些答案所声称的相反，SQL标准在这个主题上并不是沉默的。

自SQL:2003以来，您已经能够使用“窗口函数”来跳过行和限制结果集。

在SQL:2008中，添加了一个稍微简单的方法，使用
偏移__abc0行 FETCH FIRST n ROWS ONLY

就我个人而言，我不认为SQL:2008的加入是真的有必要的，所以如果我是ISO，我会把它排除在一个已经相当大的标准之外。

小开

难以置信，你能找到一个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
小开 
SELECT * FROM emp a
WHERE  n = (
SELECT COUNT( _rowid)
FROM emp b
WHERE a. _rowid >= b. _rowid
);
小开

对于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。

小开

SQL 2005及以上版本内置了这个特性。使用ROW_NUMBER()函数。对于带有<<>>样式浏览:

语法:

SELECT
*
FROM
(
SELECT
ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
*
FROM
Table_1
) sub
WHERE
RowNum = 23
小开

从表中选择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
小开

例如，如果你想在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号换成任何你想要的数字。

小开

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

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表中获得第五次最高金额记录。您可以匹配字段和表名并提出解决方案。

小开

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
小开

在SQL Server上验证:

Select top 10 * From emp
EXCEPT
Select top 9 * From emp

这将给你第十排emp表!

小开 
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
小开

没有什么花哨的，没有特殊的功能，以防你像我一样使用Caché……

SELECT TOP 1 * FROM (
SELECT TOP n * FROM <table>
ORDER BY ID Desc
)
ORDER BY ID ASC

假设您有一个可以信任的ID列或日期戳列。

小开

这就是我在DB2 SQL中如何做的，我相信RRN(相对记录号)由O/S存储在表中;

SELECT * FROM (
SELECT RRN(FOO) AS RRN, FOO.*
FROM FOO
ORDER BY RRN(FOO)) BAR
WHERE BAR.RRN = recordnumber
小开 
select * from
(select * from ordered order by order_id limit 100) x order by
x.order_id desc limit 1;

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

小开

在我看来，为了提高效率，您需要1)生成一个小于数据库记录数量的0到1之间的随机数，2)能够选择该位置的行。不幸的是，不同的数据库有不同的随机数生成器，以及在结果集中的某个位置选择一行的不同方法——通常您指定要跳过多少行和需要多少行，但不同的数据库有不同的做法。下面是在SQLite中为我工作的一些东西:

select *
from Table
limit abs(random()) % (select count(*) from Words), 1;

它确实依赖于能够在limit子句中使用子查询(在SQLite中是limit <recs to skip>，<recs to take>)作为数据库元数据的一部分，选择表中的记录数量应该特别有效，但这取决于数据库的实现。此外，我不知道该查询是否会在检索第n条记录之前构建结果集，但我希望它不需要这样做。注意，我没有指定“order by”子句。“按”主键之类的东西“排序”可能更好，它将有一个索引——如果数据库在不构建结果集的情况下无法从数据库本身获取第n条记录，那么从索引中获取第n条记录可能会更快。

小开

在Oracle 12c中，你可以在ORDER BY中使用OFFSET..FETCH..ROWS选项

例如，要从顶部获取第3条记录:

SELECT *
FROM   sometable
ORDER BY column_name
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;
小开

对于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;
小开

最合适的答案，我已经看到这文章sql server

WITH myTableWithRows AS (
SELECT (ROW_NUMBER() OVER (ORDER BY myTable.SomeField)) as row,*
FROM myTable)
SELECT * FROM myTableWithRows WHERE row = 3
小开

如果你想查看本机功能: MySQL, PostgreSQL, SQLite和Oracle (基本上SQL Server似乎没有这个功能)你实际上可以使用NTH_VALUE窗口函数。 Oracle来源:Oracle函数:NTH_VALUE

我实际上已经在我们的Oracle DB中做了一些比较第一行(排序后)和第二行(同样，排序后)的实验。 代码看起来类似于这样(如果你不想去链接):

SELECT DISTINCT dept_id
, NTH_VALUE(salary,2) OVER (PARTITION BY dept_id ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "SECOND HIGHEST"
, NTH_VALUE(salary,3) OVER (PARTITION BY dept_id ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS "THIRD HIGHEST"
FROM employees
WHERE dept_id in (10,20)
ORDER
BY dept_id;

我发现它很有趣，我希望他们能让我用它。

小开

我在这里有点晚了，但我已经在不需要窗口或使用的情况下做到了这一点

WHERE x IN (...)

SELECT TOP 1
--select the value needed from t1
[col2]
FROM
(
SELECT TOP 2 --the Nth row, alter this to taste
UE2.[col1],
UE2.[col2],
UE2.[date],
UE2.[time],
UE2.[UID]
FROM
[table1] AS UE2
WHERE
UE2.[col1] = ID --this is a subquery
AND
UE2.[col2] IS NOT NULL
ORDER BY
UE2.[date] DESC, UE2.[time] DESC --sorting by date and time newest first
) AS t1
ORDER BY t1.[date] ASC, t1.[time] ASC --this reverses the order of the sort in t1

它似乎工作得相当快，尽管公平地说，我只有大约500行数据

这在MSSQL中有效

小开 
WITH r AS (
SELECT TOP 1000 * FROM emp
)
SELECT * FROM r
EXCEPT
SELECT TOP 999 FROM r

这将给出SQL Server中的第1000行。


提交答案