[color=green]几种数据库的分页查询,在这里整理一下。老记不住![/color]
[b](1)Oracle分页查询:[/b]
SELECT * FROM
(
SELECT A.*,ROWNUM RN
FROM (SELECT * FROM TableName) A
WHERE ROWNUM<=40
)
WHERE RN>=20
----------------------------------------------------------
SELECT * FROM (SELECT rownum r,* from table_name) A
WHERE A.r>m AND A.r<=n
结果返回的是第m+1行到第n行的数据集。比如:
SELECT * FROM (SELECT rownum r,* from table_name) A
WHERE A.r>5 AND A.r<=10
的意思就是返回包含第6行到第10行的数据结果集。
[b](2)MicroSoft SQL Server 2000:[/b]
select top 5 * from
(select top 15 * from TableName order by id asc) Table_别名
order by id desc
--------------------------------------------------------------------
SELECT * FROM
(SELECT TOP m * FROM
(SELECT TOP n * FROM table_name) AS A
ORDER BY column_name DESC
) B
ORDER BY column_name
获得的结果集数据为第n-m+1行到第n行。
对整个过程的解释:首先按照升序得到前n行的结果集A,然后按照降序从A中得到后m行的结果集B,最后按照升序对B进行重新排序,返回结果集。比如:
SELECT * FROM
(SELECT TOP 5 * FROM
(SELECT TOP 10 * FROM table_name) AS A
ORDER BY column_name DESC
) B
的意思就是返回包含第6行到第10行的数据结果集。
[b](3)Mysql:[/b]
--查询出前10条记录(实际是就是从0开始)等价于: limit 0,10
select * from TableName limit 10
--查询出第10条(包括)开始的共20条记录(最先是从0开始)
select * from TableName limit 10,20
[b](1)Oracle分页查询:[/b]
SELECT * FROM
(
SELECT A.*,ROWNUM RN
FROM (SELECT * FROM TableName) A
WHERE ROWNUM<=40
)
WHERE RN>=20
----------------------------------------------------------
SELECT * FROM (SELECT rownum r,* from table_name) A
WHERE A.r>m AND A.r<=n
结果返回的是第m+1行到第n行的数据集。比如:
SELECT * FROM (SELECT rownum r,* from table_name) A
WHERE A.r>5 AND A.r<=10
的意思就是返回包含第6行到第10行的数据结果集。
[b](2)MicroSoft SQL Server 2000:[/b]
select top 5 * from
(select top 15 * from TableName order by id asc) Table_别名
order by id desc
--------------------------------------------------------------------
SELECT * FROM
(SELECT TOP m * FROM
(SELECT TOP n * FROM table_name) AS A
ORDER BY column_name DESC
) B
ORDER BY column_name
获得的结果集数据为第n-m+1行到第n行。
对整个过程的解释:首先按照升序得到前n行的结果集A,然后按照降序从A中得到后m行的结果集B,最后按照升序对B进行重新排序,返回结果集。比如:
SELECT * FROM
(SELECT TOP 5 * FROM
(SELECT TOP 10 * FROM table_name) AS A
ORDER BY column_name DESC
) B
的意思就是返回包含第6行到第10行的数据结果集。
[b](3)Mysql:[/b]
--查询出前10条记录(实际是就是从0开始)等价于: limit 0,10
select * from TableName limit 10
--查询出第10条(包括)开始的共20条记录(最先是从0开始)
select * from TableName limit 10,20