我有兴趣学习一些(理想情况下)与数据库无关的从数据库表中选择第 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's Weblog
更新:有关 SQL 标准,请参阅 Troels Arvin's answer。 Troels,你有任何我们可以引用的链接吗?
OrderNo N
,则在表中引入 OrderSequenceNo 列,并在创建新订单时从 独立序列生成器 生成它。
offset x fetch first y rows only
。目前由(至少)Postgres、Oracle12、DB2 支持。
在标准的可选部分中有一些方法可以做到这一点,但是很多数据库都支持他们自己的方法。
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 标准定义的 windowing functions,但它们很尴尬,所以大多数人使用(非标准)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() 函数。它非常适合具有 << Prev and Next >> 样式浏览的网页:
句法:
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 服务器(我认为),但应该适用于不支持 ROW_NUMBER() 的旧版本。
在 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,我会将它排除在已经相当大的标准之外。
1 个小变化:n-1 而不是 n。
select *
from thetable
limit n-1, 1
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
当我们过去在 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
它并不优雅,速度也不快,但它确实有效。
OuterPageSize
计算下方的 IF / ELSE IF
块 - 在第 1 页和第 2 页上,它们会将 OuterPageSize
值降回 10。在第 3 页(第 21-25 行),计算将正确返回 5 ,并且在所有第 4 页和更大的页面上,计算的负数结果将被 0 替换(尽管此时立即返回空数据行可能会更快)。
在 Oracle 12c 中,您可以将 OFFSET..FETCH..ROWS
选项与 ORDER BY
一起使用
例如,要从顶部获取第三条记录:
SELECT *
FROM sometable
ORDER BY column_name
OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;
这是您困惑的快速解决方案。
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 表中获得最高金额的最后第五条记录。您可以适合您的字段和表名称并提出解决方案。
甲骨文:
select * from (select foo from bar order by foo) where ROWNUM = x
where ROWNUM = x
仅适用于 Oracle DB 中的 x = 1。即 where ROWNUM = 2
不会返回任何行。
添加:
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 行的信息。一定要在之后放入 rowcount 0。
这是我最近为 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);
但实际上,这一切不只是为了良好的数据库设计而摆在首位的小把戏吗?有几次我需要这样的功能,这是一个简单的一次性查询来制作快速报告。对于任何真正的工作,使用这些技巧都会招来麻烦。如果需要选择特定的行,那么只需有一列具有顺序值并完成它。
没什么花哨的,没有特殊功能,以防你像我一样使用 Caché...
SELECT TOP 1 * FROM (
SELECT TOP n * FROM <table>
ORDER BY ID Desc
)
ORDER BY ID ASC
假设您有一个可以信任的 ID 列或 datestamp 列。
对于 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;
LIMIT n,1 在 MS SQL Server 中不起作用。我认为这只是唯一不支持该语法的主要数据库。公平地说,它不是 SQL 标准的一部分,尽管它得到了如此广泛的支持,它应该是。除了 SQL server LIMIT 之外的所有东西都很好用。对于 SQL 服务器,我还没有找到一个优雅的解决方案。
在 Sybase SQL Anywhere 中:
SELECT TOP 1 START AT n * from table ORDER BY whatever
不要忘记 ORDER BY 否则它毫无意义。
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
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
我在这里参加聚会有点晚了,但我已经做到了,不需要开窗或使用
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
难以置信你能找到一个执行这个的 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
(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;
它确实取决于能够在限制子句中使用子查询(在 SQLite 中是 LIMIT
我在 sql server 的 this 文章中看到的最合适的答案
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 Functions: 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 rownumber = n
只获得第 n 行吗?