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

mysql sql database oracle postgresql

646832 观看

29回复

11877 作者的声誉

我有兴趣学习一些(理想情况下)数据库无关的方法来从数据库表中选择第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,您有任何我们能引用的链接吗?

作者: Charles Roper 的来源 发布者: 2008 年 8 月 19 日

回应 (29)


2

13589 作者的声誉

加:

LIMIT n,1

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

作者: Andrew G. Johnson 发布者: 19.08.2008 05:14

2

51640 作者的声誉

LIMIT n,1在MS SQL Server中不起作用。我认为这只是唯一不支持该语法的主要数据库。公平地说,它不是SQL标准的一部分,尽管它应该得到如此广泛的支持。除了SQL服务器LIMIT之外的一切都很棒。对于SQL Server,我一直无法找到优雅的解决方案。

作者: Kibbee 发布者: 19.08.2008 05:18

2

15632 作者的声誉

这是我最近为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);
作者: Greg Hurlman 发布者: 19.08.2008 05:19

27

993 作者的声誉

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

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

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

作者: Ellen Teapot 发布者: 19.08.2008 05:20

16

4069 作者的声誉

我怀疑这是非常低效的,但这是一个非常简单的方法,它适用于我尝试过的小数据集。

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()的旧版本。

作者: Tim Saunders 发布者: 19.08.2008 05:28

88

20315 作者的声誉

PostgreSQL中LIMIT/ OFFSET语法是:

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

此示例选择第21行。OFFSET 20告诉Postgres跳过前20条记录。如果您没有指定一个ORDER BY条款,则无法保证您将获得哪条记录,这很少有用。

显然,SQL标准对疯狂窗口函数之外的限制问题保持沉默,这就是每个人以不同方式实现它的原因。

作者: Neall 发布者: 19.08.2008 05:31

6

4403 作者的声誉

当我们以前在MSSQL 2000中工作时,我们做了所谓的“三重翻转”:

EDITED

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

它并不优雅,并不快,但它确实有效。

作者: Adam V 发布者: 19.08.2008 05:33

6

173288 作者的声誉

甲骨文:

select * from (select foo from bar order by foo) where ROWNUM = x
作者: Mark Harrison 发布者: 19.08.2008 06:51

2

0 作者的声誉

但实际上,首先,这不仅仅是用于良好数据库设计的客厅技巧吗?我需要几次这样的功能,这是一个简单的一次性查询来快速报告。对于任何实际工作,使用这样的技巧会引起麻烦。如果需要选择特定的行,那么只需要一个具有顺序值的列并完成它。

作者: John Dyer 发布者: 19.08.2008 07:06

1

36304 作者的声誉

在Sybase SQL Anywhere中:

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

不要忘记ORDER BY或它没有意义。

作者: Graeme Perrow 发布者: 19.08.2008 07:06

299

33039 作者的声誉

决定

有很多方法可以在标准的可选部分中执行此操作,但许多数据库都支持自己的方法。

讨论这个和其他事情的一个非常好的网站是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。

作者: Henrik Gustafsson 发布者: 19.08.2008 07:22

11

45572 作者的声誉

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

select *
from thetable
limit n-1, 1
作者: Nick Berardi 发布者: 19.08.2008 07:25

9

4301 作者的声誉

与某些答案所声称的相反,SQL标准并未对此主题保持沉默。

从SQL:2003开始,您就可以使用“窗口函数”来跳过行并限制结果集。

在SQL:2008中,使用了一种稍微简单的方法<br> OFFSET <em>skip</em> ROWS FETCH FIRST <em>n</em> ROWS ONLY

就个人而言,我不认为SQL:2008的添加是真的需要,所以如果我是ISO,我会把它保留在已经相当大的标准之外。

作者: Troels Arvin 发布者: 03.09.2008 10:39

0

28 作者的声誉

令人难以置信的是你可以找到执行这个的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
作者: jrEving 发布者: 06.03.2009 01:30

1

0 作者的声誉

SELECT * FROM emp a
WHERE  n = (SELECT COUNT( _rowid)
              FROM emp b
             WHERE a. _rowid >= b. _rowid);
作者: Rahul Sharma 发布者: 08.06.2009 05:10

1

0 作者的声誉

对于SQL Server,按行号排序的通用方法如下:SET ROWCOUNT @row - @ row =您要处理的行号。

例如:

set rowcount 20 - 将行设置为第20行

从dbo.sandwich选择肉类,奶酪 - 从第20行的表中选择列

set rowcount 0 - 将rowcount设置回所有行

这将返回第20行的信息。务必在之后输入rowcount 0。

我知道noobish,但我是一个SQL菜鸟,我已经使用过了,所以我能说什么呢?

作者: Eric 发布者: 22.06.2009 05:00

19

863 作者的声誉

SQL 2005及更高版本内置了此功能。使用ROW_NUMBER()函数。它非常适合浏览“上一页和下一页”样式的网页:

句法:

SELECT
    *
FROM
    (
        SELECT
            ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
            *
        FROM
            Table_1
    ) sub
WHERE
    RowNum = 23
作者: Ben Breen 发布者: 09.07.2009 03:00

1

14 作者的声誉

T-SQL - 从表中选择第N个RecordNumber

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
作者: Sangeeth Krishna 发布者: 19.11.2010 07:02

2

900 作者的声誉

例如,如果要在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就可以了。

作者: E-A 发布者: 30.12.2011 08:41

4

512 作者的声誉

这是您混乱的快速解决方案。

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表中获得最高金额的第五个最后记录。您可以适合您的字段和表名称并提出解决方案。

作者: Amit Shah 发布者: 17.05.2012 09:29

6

1466 作者的声誉

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
作者: Aditya 发布者: 19.02.2014 04:17

12

294 作者的声誉

在SQL Server上验证它:

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

这将给你第10个emp表的行!

作者: Rameshwar Pawale 发布者: 16.10.2014 10:59

1

125 作者的声誉

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
作者: Arjun Chiddarwar 发布者: 29.01.2015 09:09

0

7768 作者的声誉

没有什么花哨,没有特殊功能,如果你像我一样使用Caché......

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

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

作者: Scott Beeson 发布者: 01.10.2015 01:16

0

69 作者的声誉

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

SELECT * FROM (                        
   SELECT RRN(FOO) AS RRN, FOO.*
   FROM FOO                         
   ORDER BY RRN(FOO)) BAR             
 WHERE BAR.RRN = recordnumber
作者: RDKells 发布者: 18.11.2016 03:12

0

81 作者的声誉

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

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

作者: Dwipam Katariya 发布者: 02.06.2017 08:26

0

837 作者的声誉

在我看来,为了提高效率,您需要1)生成一个介于0到1之间的随机数,小于数据库记录的数量,以及2)能够选择该位置的行。遗憾的是,不同的数据库具有不同的随机数生成器以及在结果集中某个位置选择行的不同方法 - 通常指定要跳过的行数和所需的行数,但对于不同的数据库,它们的执行方式不同。这是在SQLite中适合我的东西:

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

它确实依赖于能够在limit子句中使用子查询(在SQLite中是LIMIT )选择表中的记录数应该特别有效,作为数据库的一部分元数据,但这取决于数据库的实现。此外,我不知道查询是否会在检索第N条记录之前实际构建结果集,但我希望它不需要。请注意,我没有指定“order by”子句。可能更好的“排序”类似主键,它将具有索引 - 如果数据库无法从数据库本身获取第N条记录而不构建结果集,则从索引获取第N条记录可能会更快。

作者: user1738579 发布者: 17.07.2017 06:33

1

19635 作者的声誉

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

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

SELECT * 
FROM   sometable
ORDER BY column_name
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;
作者: Kaushik Nayak 发布者: 05.02.2018 12:43

0

2934 作者的声誉

对于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;
作者: nPcomp 发布者: 20.06.2018 05:48
32x32