Oracle、Sqlserver与Mysqkll分页查询

Oracle

   SELECT * FROM (
           SELECT A.*, ROWNUM RN   FROM (

            SELECT * FROM TABLE_NAME       ) A

            WHERE ROWNUM <= 40

      ) WHERE RN >= 21


Mysql

    几千上万条数据直接用自带的limit函数就能解决

    select * from table limit(m,n);//从m开始取n条数据,索引从0开始表示第一条数据,从m+1条数据开始取那条数据

    如果是百万级别的数据就得换写法了

    select * from table where (select * from table limit(500000,1) limit 10;


SqlServer    

  1、SELECT TOP 页大小 * FROM table1 WHERE id NOT IN ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id ) ORDER BY id

   2、SELECT TOP 页大小 * FROM table1 WHERE id > ( SELECT ISNULL(MAX(id),0) FROM ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id ) A ) ORDER BY id

  3、SELECT TOP 页大小 * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1 ) A WHERE RowNumber > 页大小*(页数-1)

Oracle、Sqlserver与Mysqkll分页查询,布布扣,bubuko.com

Oracle、Sqlserver与Mysqkll分页查询

上一篇:Oracle 使用连接显示多个表中的数据


下一篇:Oracle 对行进行分组-group by、having