数据库分页语句(mysql,oracle,sqlserver,DB2)

mysql:
[quote]
select * from 表名 where pid=0 limit ((当前页-1)*每页显示多少), 每页显示多少

[/quote]

oracle:

[quote] select * from(

select rownum as rn,source.* from(
sql语句
) source where rownum <= (当前页*每页显示多少)
) result where rn >= ((当前页-1)*每页显示多少)[/quote]
sqlserver:

[quote] select top 每页显示多少 * from 表名 where 主键列 not in

(select top (当前页-1)*每页显示多少 主键列 from 表名 order by 主键列 )

order by 主键列

如何主键列是自动增长,则可以这样增强查询速度

select top 每页显示多少 * from 表名 where 主键列 >

(select max(主键列) from (select (当前页-1)*每页显示多少 主键列 from 表名 order by 主键列 ))

order by 主键列[/quote]

DB2

[quote]select * from (
select ROW_NUMBER() OVER() AS ROWNUM,source.*
from (

sql语句
)source
) a
where ROWNUM >= ((当前页-1)*每页显示多少) and ROWNUM <=(当前页*每页显示多少)[/quote]


本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/xiaoping8411/archive/2008/07/21/2682520.aspx