SQLServer 、Oracle、MYSQL分页sql话语

SQLServer 、Oracle、mysql分页sql语句
trt_teacher_temp 是表名
SQLServer 2000 的：
(1) 利用not in 和 select top
select top 10 * from trt_teacher_temp where c_id not in(select top 20 c_id from trt_teacher_temp)
第一个10=pageSize 表示 每页大小，第二个 10=(pageNo-1)*pageSize 表示 起初开始
(2) 利用 ID大于多少和 select top
select top 10 * from trt_teacher_temp where(c_id >(select max(c_id) from (select top 10 c_id from trt_teacher_temp
   order by c_id) as t))order by c_id

Oracl 的：
(1) select * from  (select rownum rn,a.* from ( select * from trt_teacher_temp )a where rownum<=20) where rn>10

20 = pageSize*pageNo    10= (pageNo-1)*pageSize

(2)
select a.* from
( select rownum num ,t.* from trt_teacher_temp t order by id asc )a where a.num>=11 and a.num<21
11= pageSize*(pageNo-1)+1   21=pageSize*(pageNo-1)+1+pageSize

(3)
select * from tb_zfzx where rownum<=20 minus select * from tb_zfzx where rownum<=10
结论：minus返回的总是左边表中的数据，它返回的是差集,去同留异。

MYSQL的：
select * from trt_teacher_temp limit 20,10
20=(pageNo-1)*pageSize  10=pageSize

Oracle、DB2、SQLSERVER、Mysql、Access分页SQL语句梳理
见如下链接
http://www.blogjava.net/sxyx2008/archive/2010/09/16/332193.html

喜欢 (0)赏

[搞代码]

分享 (0)