Oracle、SQL Server、MySQL分页方法

测试用例：查询TEST_TABLE表中TEST_COLUMN列的第10-20条数据

 

1，Oracle分页方法

 

SELECT A.* FROM
(
    SELECT ROWNUM ROWNO, B.* FROM
    (
        SELECT * FROM TEST_TABLE
        WHERE TEST_COLUMN = 'TEST'
        ORDER BY TEST_TIME DESC
    )B
    WHERE ROWNUM  <= 20
) A
WHERE A.ROWNO >= 10;

 

2，SQL Server分页方法

 

SELECT TOP 20 * FROM TEST_TABLE
WHERE TEST_COLUMN NOT IN
(
    SELECT TOP 10 TEST_COLUMN
    ORDER BY TEST COLUMN
)
ORDER BY TEST_COLUMN;

 

3，MySQL分页方法

 

SELECT TEST_COLUMN FROM TEST_TABLE LIMIT 10,10;