SQL - 常用資料庫分頁

一、MSSQL

SELECT TOP pageSize *                         -- [fields]
FROM TABLE_NAME 
WHERE id NOT IN (
    SELECT TOP pageSize*(pageNow-1) id 
    FROM TABLE_NAME 
    ORDER BY id
)
ORDER BY id

二、MySQL 

SELECT *                                      -- [fields]
FROM TABLE_NAME 
LIMIT pageNow*(pageSize-1), pageSize

三、Oracle

SELECT * 
FROM ( 
    SELECT A.*, ROWNUM RN 
    FROM ( 
        SELECT *                              -- [fields]
        FROM TABLE_NAME [where ...]
    ) A 
    WHERE ROWNUM <= pageNow*pageSize
) 
WHERE RN >= ((pageNow-1)*pageSize+1)

四、PostgreSQL

SELECT *                                      -- [fields]
FROM TABLE_NAME 
LIMIT pageSize offset pageSize*(pageNow-1);

五、備註
    pageSize: 頁面大小

    pageNow: 當前頁碼(從1開始)