我感兴趣的是从数据库表中选择第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，你有我们可以引用的链接吗?
当前回答
我在这里有点晚了，但我已经在不需要窗口或使用的情况下做到了这一点
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中有效
其他回答
当我们在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
它并不优雅，速度也不快，但它奏效了。
我不确定其他的，但我知道SQLite和MySQL没有任何“默认”行排序。至少在这两种方言中，下面的代码片段从the_table中抓取了第15个条目，按添加的日期/时间排序:
SELECT *
FROM the_table
ORDER BY added DESC
LIMIT 1,15
(当然，您需要添加一个DATETIME字段，并将其设置为条目添加的日期/时间…)
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
SQL 2005及以上版本内置了这个特性。使用ROW_NUMBER()函数。它非常适合浏览<< Prev和Next >>样式的网页:
语法:
SELECT
*
FROM
(
SELECT
ROW_NUMBER () OVER (ORDER BY MyColumnToOrderBy) AS RowNum,
*
FROM
Table_1
) sub
WHERE
RowNum = 23
Oracle:
select * from (select foo from bar order by foo) where ROWNUM = x
