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

最后发布: 2008-08-19 17:13:11

问题

我有兴趣学习一些（理想情况下）从数据库表中选择第n行的数据库不可知方法。 看看如何使用以下数据库的本机功能实现此目标也将很有趣：

  • SQL服务器
  • 的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

更新：有关SQL标准，请参见Troels Arvin的答案 。 Troels，您有我们可以引用的任何链接吗？

mysql sql database oracle postgresql
回答

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

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中使用。

回答

PostgreSQL支持SQL标准定义的窗口函数 ，但是它们很尴尬，所以大多数人使用（非标准） LIMIT / OFFSET ： 

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

本示例选择第21行。 OFFSET 20告诉Postgres跳过前20条记录。 如果您未指定ORDER BY子句，则无法保证您将获得哪条记录，这很少有用。

回答

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

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

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

回答

SQL 2005及更高版本具有此内置功能。 使用ROW_NUMBER（）函数。 对于具有<<上一页和下一页>>样式浏览的网页而言，它非常有用：

句法： 

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

回答

我怀疑这是非常低效的，但却是一种非常简单的方法，该方法适用于我尝试过的小型数据集。 

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 Server（我认为），但应在不支持ROW_NUMBER（）的旧版本上运行。

回答

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

select *
from thetable
limit n-1, 1

回答

在SQL Server上验证： 

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

这将给您emp表的第10行！

回答

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

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

在SQL：2008中，使用
OFFSET skip ROWS FETCH FIRST n ROWS ONLY

就我个人而言，我不认为确实需要添加SQL：2008，所以如果我是ISO，我将使它脱离本来就相当大的标准。

回答

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

它不是很优雅，也不是很快，但是确实有效。

回答

SQL服务器

从顶部选择第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

回答

甲骨文： 

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

回答

这是您的困惑的快速解决方案。 

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

回答

在Oracle 12c中，您可以将OFFSET..FETCH..ROWS选项与ORDER BY

例如，要从顶部获得第三条记录： 

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

回答

加： 

LIMIT n,1

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

回答

例如，如果要在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号即可。

回答

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

回答

LIMIT n，1在MS SQL Server中不起作用。 我认为这只是唯一不支持该语法的主要数据库。 公平地说，它不是SQL标准的一部分，尽管它得到了应有的广泛支持。 除了SQL Server之外，LIMIT在所有情况下都非常有效。 对于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);

回答

但是，真的，所有这些难道不是真的只是为了良好的数据库设计而准备的窍门吗？ 几次我需要这样的功能，这是一个简单的一次性查询即可快速生成报告。 对于任何实际工作，使用这样的技巧都会带来麻烦。 如果需要选择特定的行，则只需包含一列具有顺序值的列即可。

回答

在Sybase SQL Anywhere中： 

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

不要忘记ORDER BY或它毫无意义。

回答

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

回答

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

回答

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

回答

SELECT * FROM emp a
WHERE  n = (SELECT COUNT( _rowid)
              FROM emp b
             WHERE a. _rowid >= b. _rowid);

回答

万一你像我一样使用Caché，那就没什么花哨的了，没有特殊的功能... 

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

假设您有一个ID列或datestamp列，则可以信任。

回答

这就是我在DB2 SQL中执行此操作的方式，我相信R / O（相对记录号）是由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>，<要取的recs>）选择表中的记录数应该特别有效，因为它是数据库的一部分元数据，但这取决于数据库的实现。 另外，我不知道查询是否会在检索第N条记录之前真正构建结果集，但我希望它不需要。 请注意，我没有指定“ order by”子句。 最好对诸如主键之类的东西进行“排序”，该主键将具有索引-如果数据库无法在不构建结果集的情况下从数据库本身获取第N条记录，则从索引获取第N条记录可能会更快。 。

回答

您可以使用以下一种： 

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

回答

令人难以置信的是，您会发现执行此操作的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

1xcode 8.2 swift 3核心数据 - 使用未解析的标识符

2InfoWindow内容仅动态更改一部分

3未从UIViewController中的UITableView调用prepareForSegue

4如何获取当前正在运行的hadoop作业的名称？

5减少heatmap.2图上的列宽

6如何用右移运算符将整数除以常量整数？ [重复]

7python脚本在解释器中运行，但不能从命令行运行

8在两个不同的视图中使用相同的模型，而在第二个视图中不获取验证消息

9将pandas.read_html中的列表转换为数据框

10WatchKit原生应用程序

11XCode中。 如何在同一个开发平台上运行两个版本。 机器：版本3.2.2（预发行版）和版本3.2.1

12获取对象中变量的名称

13如何在不使用数据集或数据表的情况下向datagridview添加新行？

14如何用php删除特定的html元标记？

15使用具有&gt; 1的相同列1而其他列2选择行

16正则表达式用于提取信息

17在实体框架查询中将字符串时间转换为日期时间或时间跨度

18使用换行符添加内容文本文件

19RegularExpression验证程序：不应允许单个零

20$ http JSON请求在angularjs中不起作用