SQL分页SQL(MySQL,SQLServer,Oracle,DB2)

1. MySQL

select * from [表名称] limit [起始位置], [比数]

例如:

查询表t中第5笔开始, 往后的10笔资料(起始位置从0开始)

select * from t limit 5, 10

2. SQL Server

需要对资料增加一个序号, 然后再对序号加入分页条件(起始位置从1开始)

select *
  from (select *,row_number() over (order by user_id) rn from emp_user) t
 where rn between 1 and 10

3. Oracle

如果是对资料排序后在分页, 需要使用以下的三层结构(起始位置从1开始)

select * from (  
    select page.*,rownum rn from (select * from t order by t.col) page   
     where rownum <= 20  
)  
where rn > 10
4. DB2

与SQL Server类似, 但是可以不对资料排序(起始位置从1开始)

对资料排序

select * from (
    select t.*, ROW_NUMBER() OVER(ORDER BY CARD_ID asc) AS ROWNUM from CARD t ) a
where ROWNUM > 20 and ROWNUM <=30

不对资料排序

select * from (
    select t.*, ROW_NUMBER() OVER() AS ROWNUM from CARD t ) a
where ROWNUM > 20 and ROWNUM <=30