SQL Server 分頁查詢四種經典寫法

方案一：NOT IN 子查詢

SELECT TOP N * FROM 文章表
WHERE id NOT IN (SELECT TOP M id FROM 文章表 ORDER BY id DESC)
ORDER BY id DESC

方案二：子查詢取最小 ID

SELECT TOP N * FROM 文章表
WHERE id < (SELECT MIN(id) FROM (SELECT TOP M id FROM 文章表 ORDER BY id DESC) AS tblTmp)
ORDER BY id DESC

方案三：游標分頁存儲過程（完整範例）

CREATE DATABASE mydb
GO
USE mydb
GO
CREATE TABLE news (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(50)
)
GO
-- 插入測試資料 20 筆...
INSERT INTO news VALUES('001','aaa')
-- ...略...
INSERT INTO news VALUES('020','sss')
GO
SELECT * FROM news
GO
CREATE PROC proc_cursor
    @pagesize INT,
    @pageindex INT,
    @pagetotal INT OUTPUT
AS
BEGIN
    DECLARE @total INT, @start INT, @end INT, @id VARCHAR(10), @name VARCHAR(10), @i INT
    DECLARE mycur SCROLL CURSOR FOR SELECT * FROM news ORDER BY ID
    OPEN mycur
    SET @total = @@CURSOR_ROWS
    IF @total > 0
    BEGIN
        IF @total % @pagesize = 0
            SET @pagetotal = @total / @pagesize
        ELSE
            SET @pagetotal = @total / @pagesize + 1
        -- 後續邏輯：FETCH ABSOLUTE 定位頁首，逐筆讀取...
    END
    CLOSE mycur
    DEALLOCATE mycur
END
GO

方案四：SQL Server 2012+ OFFSET/FETCH（現代標準寫法）

SELECT * FROM 文章表
ORDER BY id DESC
OFFSET (@PageIndex - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY

建議： SQL Server 2012 及以上版本請優先使用 OFFSET/FETCH，效能最佳且語法簡潔；舊版本可用方案二（配合索引）或方案三（小資料量）。