Oracle / MySQL / SQL Server分页查询公式整理

/* Oracle */

SELECT * FROM (
    SELECT ROWNUM r, tab.* FROM tab_name tab
		WHERE ROWNUM <= pageSize * pageNo) temp
WHERE r > pageSize * pageNo - pageSize;

SELECT *  FROM (
    SELECT ROWNUM rn, temp.* FROM (
        SELECT * FROM tab_name tab ORDER BY tab.pk_column DESC) temp
		WHERE ROWNUM < end_num)
WHERE rn > start_num;

/* MySQL */

SELECT * FROM tab_name LIMIT(pageNo - 1) * pageSize, pageSize; (不推荐)

SELECT * FROM tab_name WHERE pk_column >= (
    SELECT pk_column FROM tab_name 
    LIMIT (pageNo - 1) * pageSize, 1)
LIMIT pageSize;

SELECT * FROM tab_name AS main 
JOIN (SELECT pk_column FROM tab_name LIMIT pageNo - 1 * pageSize, pageSize) AS sub 
ON main.pk_column = sub.pk_column;

/* SQL Server */

SELECT TOP pageSize * FROM (
    SELECT ROW_NUMBER() OVER(
        ORDER BY pk_column DESC) 
				AS rownumber, * FROM tab_name) temp
WHERE rownumber > ((pageNo - 1) * page