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开始)