Sql Server 大数据量查询

常规条件分页查询

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 )