分页SQL

Oracle

rownum三层嵌套查询
1)先排序
2)得到行号
3)再规定范围

select y.* from 
(
  select rownum as r2,x.* 
  from (
     select e.* from emp e order by sal desc
  ) x
) y where r2>=6 and r2<=10 

row_number函数(数据量大，效率高)

select e.*,row_number() over(partition  by deptno order by empno)as num from emp e
--partition by 按xx分组，
--order by 按YY排序。

--每个分组一个number


select x.* from(
(
select e.* ,row_number() over(order by sal) as num from emp e
)x
)where num>=5 and num<=15

Mysql分页采用limt关键字

select * from t_order limit 5,10; #返回第6-15行数据
select * from  t_order limit  5; #返回前5行
select * from  t_order limit  0,5; #返回前5行

Mssql 2000分页采用top关键字(20005以上版本也支持关键字rownum)

Select top 10 * from t_order where id not in 
(select id from t_order where id>5 ); //返回第6到15行数据

其中10表示取10记录 5表示从第5条记录开始取