常規(guī)條件分頁查詢

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 '唯一標(biāo)識(shí)',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 

常規(guī)加嵌套

-- In ROW_NUMBER 嵌套分頁寫法，特定情況下性能會(huì)更好（降低排序過程中內(nèi)存使用）
-- 使用場(chǎng)景要求：
-- 1狡门、大數(shù)據(jù)量查詢
-- 2靠欢、必須包含主鍵列（示例中為ID）
-- 3俄占、表返回?cái)?shù)據(jù)列過多号醉，或包含長文本列
-- 4剧罩、如果數(shù)據(jù)精度要求不高固翰，還可表名后自行增加 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 '唯一標(biāo)識(shí)',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 '唯一標(biāo)識(shí)',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 )