常规条件分页查询
SELECT column_0 AS 'stationId',column_2 AS 'cityId',column_7 AS 'dataTime',column_8 AS 'maxPollution', column_5 AS 'aqiLevel',column_15 AS 'type',ID AS '唯一标识',column_3 AS 'cityName',column_14 AS 'updatetime', column_1 AS 'stationName',column_4 AS 'AQI',column_12 AS 'PM25',column_11 AS 'PM10',column_6 AS 'CO', column_9 AS 'NO2',column_10 AS 'O3',column_13 AS 'SO2'
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY Space0046A.ID DESC ) AS rn,
Space0046A.column_0,
Space0046A.column_2,Space0046A.column_7,
Space0046A.column_8,Space0046A.column_5,
Space0046A.column_15,Space0046A.ID,
Space0046A.column_3,Space0046A.column_14,
Space0046A.column_1,Space0046A.column_4,
Space0046A.column_12,Space0046A.column_11,
Space0046A.column_6,Space0046A.column_9,
Space0046A.column_10,Space0046A.column_13
FROM Space0046A
WHERE 1=1 )t
WHERE rn<=1000010
AND rn>1000000
常规加嵌套
-- In ROW_NUMBER 嵌套分页写法，特定情况下性能会更好（降低排序过程中内存使用）
-- 使用场景要求：
-- 1、大数据量查询
-- 2、必须包含主键列（示例中为ID）
-- 3、表返回数据列过多，或包含长文本列
-- 4、如果数据精度要求不高，还可表名后自行增加 with(nolock)
SELECT column_0 AS 'stationId',column_2 AS 'cityId',column_7 AS 'dataTime',column_8 AS 'maxPollution', column_5 AS 'aqiLevel',column_15 AS 'type',ID AS '唯一标识',column_3 AS 'cityName',column_14 AS 'updatetime', column_1 AS 'stationName',column_4 AS 'AQI',column_12 AS 'PM25',column_11 AS 'PM10',column_6 AS 'CO', column_9 AS 'NO2',column_10 AS 'O3',column_13 AS 'SO2'
FROM Space0046A
WHERE ID IN
(SELECT ID
FROM
(SELECT ID,
ROW_NUMBER() OVER(ORDER BY Space0046A.ID DESC ) AS rn
FROM Space0046A WHERE 1=1 ) data
WHERE rn<=1000010
AND rn>1000000 )
SELECT column_0 AS 'stationId',column_2 AS 'cityId',column_7 AS 'dataTime',column_8 AS 'maxPollution', column_5 AS 'aqiLevel',column_15 AS 'type',ID AS '唯一标识',column_3 AS 'cityName',column_14 AS 'updatetime', column_1 AS 'stationName',column_4 AS 'AQI',column_12 AS 'PM25',column_11 AS 'PM10',column_6 AS 'CO', column_9 AS 'NO2',column_10 AS 'O3',column_13 AS 'SO2'
FROM Space0046A with(nolock)
WHERE ID IN
(SELECT ID
FROM
(SELECT ID,
ROW_NUMBER() OVER(ORDER BY Space0046A.ID DESC ) AS rn
FROM Space0046A with(nolock) WHERE 1=1 ) data
WHERE rn<=1000010
AND rn>1000000 )
