我感兴趣的是从数据库表中选择第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,你有我们可以引用的链接吗?
在标准的可选部分中有一些方法可以做到这一点,但许多数据库都支持自己的方法。
一个非常好的网站是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中工作。
如果你想查看本机功能:
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;
我发现它很有趣,我希望他们能让我用它。