时间戳

首页 > 解决方案 > 将 LIMIT 和 OFFSET 应用于 MS SQL Server 2008 查询

sql - 将 LIMIT 和 OFFSET 应用于 MS SQL Server 2008 查询

问题描述

我需要在 MSSQL server 2008 中应用LIMIT和OFFSET原始查询（不修改它）。

假设原始查询是：

SELECT * FROM energy_usage

（但它可以是任意的 SELECT 查询）

到目前为止，这就是我想出的：

1.它可以满足我的需要，但查询会生成我不需要的额外列row_number。

WITH OrderedTable AS
(
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_number,  * FROM energy_usage
)   
SELECT * FROM OrderedTable WHERE row_number BETWEEN 1 AND 10

2.这个由于某种原因不起作用并返回以下错误。

SELECT real_sql.* FROM (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_number, * FROM (SELECT * FROM energy_usage) as real_sql) as subquery
WHERE row_number BETWEEN 1 AND 10

更常见的情况是：

SELECT real_sql.* FROM (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_number,  * FROM (real sql query) as real_sql) as subquery
WHERE row_number BETWEEN {offset} + 1 AND {limit} + {offset}

错误：

列前缀“real_sql”与查询中使用的表名或别名不匹配。

标签： sqlsql-serversql-server-2008tsql

解决方案

简单地不要把它放在SELECT列表中：

WITH OrderedTable AS
(
 SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_number,  * 
 FROM energy_usage
)   
SELECT col1, col2, col3 FROM OrderedTable WHERE row_number BETWEEN 1 AND 10;

SELECT *是常见的反模式，无论如何都应该避免。PlusORDER BY (SELECT 1)不会为您保证执行之间的稳定排序。

其次，如果您只需要十行，请使用：

 SELECT TOP 10 *
 FROM energy_usage
 ORDER BY ...

不幸的是，您不会像选择除一列之外的所有列那样得到好的结果

WITH OrderedTable AS
    (
     SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_number,  * 
     FROM energy_usage
    )   
SELECT * EXCEPT row_number FROM OrderedTable WHERE row_number BETWEEN 1 AND 10;

推荐阅读