SQL分页

  |   0 评论   |   626 浏览

    取出sql表中第31到40的记录(以自动增长ID为主键)

    sql server

    方案1:

    select top 10 * from t where id not in (select top 30 id from t order by id ) orde by id;
    

    方案2:

    select top 10 * from t where id in (select top 40 id from t order by id) order by id desc;
    

    mysql

    select * from t order by id limit 30, 10;
    

    oracle

    select * from (select rownum r, * from t where r <= 40) where r > 30;
    

    分页

    pageSize=20;

    pageNo = 5;

    1.分页技术(直接利用sql语句进行分页,效率最高和最推荐的)

    sql server

    select top 20 * from articles id not in (select top (pageNo-1) * pageSize id from articles);
    

    mysql

    select * from articles limit (pageNo-1) * pageSize , pageSize;
    

    oracle

    select * from 
           (select rownum r, A.* from 
    	       (select * from articles order by postime desc) A
    	 where rownum <=  pageNo*pageSize) tmp 
    where r > (pageNo-1) * pageSize;
    

    未完待续……

    评论

    发表评论

    validate