四种数据库分页查询的编写

时间：2016-02-02 15:04:17 阅读：266 评论：0 收藏：0 [点我收藏+]

mysql :

select * from 表名 order by 表列 desc limit 30,40;

sqlserver:

第一种写法：使用开窗函数

select * from 
     (select row_number() over(order by empno) as rownum, * from emp) as a
  where a.rownum >=30 and a.rownum <=40

第二种写法：使用子查询

select top 10 * from emp 
  where empno not in 
 (select top 30 empno from emp order by empno desc)
 order by empno desc

oracle:

第一种写法：使用开窗函数（类似sqlserver )

注意： 1)oracle 给表起别名不能加 as 。

　　　 2)rownum在oracle 中是关键字。

select * from 
     (select row_number() over(order by empno) as rownum2, * from emp) as a
  where a.rownum2 >=30 and a.rownum2 <=40

第二种写法：使用oracle自带的伪列rownum

select * from
 (select rownum ,*  from emp  order by empno desc  )  a  
 where a.rownum between 30 and 40

db2:

第一种写法：使用开窗函数

select * from 
 (select row_number() over(order by empno) as rownum, * from emp) as a

  where a.rownum >=30 and a.rownum <=40

第二种写法：使用子查询

select * from emp
 where empno not in
(select * from emp order by empno   fetch frist 30 rows only) 
order by empno   fetch frist 10 rows only

原文：http://www.cnblogs.com/zhaojiedi1992/p/zhaojiedi_sql_001.html

踩

(0)

评论一句话评论（0）

分享档案

更多>