Mysql的分页的sql语句:
方法一:使用COUNT(*)的方式
SELECT COUNT(*) FROM foo WHERE b = 1;
SELECT a FROM foo WHERE b = 1 LIMIT 100,10;
方法二:使用SQL_CALC_FOUND_ROWS
SELECT SQL_CALC_FOUND_ROWS a FROM foo WHERE b = 1 LIMIT 100, 10;
SELECT FOUND_ROWS();
第二种方式调用SQL_CALC_FOUND_ROWS之后会将WHERE语句查询的行数放在FOUND_ROWS()
之中,第二次只需要查询FOUND_ROWS()就可以查出有多少行了。
讨论这两种方法的优缺点:
首先原子性讲,第二种肯定比第一种好。第二种能保证查询语句的原子性,第一种当两个请求之间有额外的操作修改了表的时候,结果就自然是不准确的了。而第二种则不会。但是非常可惜,一般页面需要进行分页显示的时候,往往并不要求分页的结果非常准确。即分页返回的total总数大1或者小1都是无所谓的。所以其实原子性不是我们分页关注的重点。
sqlserver的分页的sql语句:
分页方案一:(利用Not In和SELECT TOP分页)
SELECT TOP 10 * FROM TestTable
WHERE (
ID NOT IN (
SELECT TOP 20 id FROM TestTable ORDER BY id)
)ORDER BY ID
sql语句句型:
SELECT TOP 页大小 *FROM TestTable
WHERE (
ID NOT IN
(SELECT TOP 页大小*页数 id FROM 表 ORDER BY id)
)ORDER BY ID
分页方案二:(利用ID大于多少和SELECT TOP分页)
SELECT TOP 10 *FROM TestTable
WHERE (
ID >(SELECT MAX(id) FROM (
SELECT TOP 20 id FROM TestTable ORDER BY id)
AS T)
)ORDER BY ID
sql语句句型:
SELECT TOP 页大小 * FROM TestTable
WHERE (
ID > (SELECT MAX(id)FROM (
SELECT TOP 页大小*页数 id FROM 表 ORDER BY id
) AS T)
)ORDER BY ID
分页方案三:(利用SQL的游标存储过程分页)
代码如下:
create procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
其它的方案:如果没有主键,可以用临时表,也可以用方案三做,但是效率会低。
建议优化的时候,加上主键和索引,查询效率会提高。
通过SQL 查询分析器,显示比较:结论是:
分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句
分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句
分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用
在实际情况中,要具体分析
SqlServer分页的存储过程:
方法一:
declare
@PageNo int ,
@pageSize int;
set @PageNo搜索 = 2
set @pageSize=20
select * from (
select row_number() over(order by object_id) rn,* from sys.objects
) tb
where rn >(@PageNo-1)*@pageSize and rn <=@PageNo*@pageSize
方法二:
还有一种方法就是将排序字段作为变量,通过动态SQL 实现,可以改成存储过程。
declare
@PageNo int ,
@pageSize int;
declare
@TableName varchar(128),
@OrderColumns varchar(500),
@SQL varchar(max);
set @PageNo = 2,
set @pageSize=20,
set @TableName = 'sys.objects'set
@OrderColumns = 'name ASC,object_id DESC'
set @SQL = 'select * from (
select row_number() over(order by '+@OrderColumns+' ) rn,* from ' +@TableName+')tb where rn >'+convert(varchar(50),(@PageNo-1)*@pageSize) +' and rn <= '+convert(varchar(50),@PageNo*@pageSize)print @SQL exec(@SQL)
Oracle分页的SQL语句:
方法一:
--1.先查询出全量
--2.使用rownum,它不支持大于号,支持小于号
--3.把全量作为一个结果集,让rownum小于当前页的结束号,
查询出一个结果集,把rownum作为一个结果
--4.把第三步的查询的结果集作为一个表
--5.在外面套一层select,把里层的rw作为查询条件,让它大于起始行号
select * from (select rownum rw, a.*
from (
select * from eb_item
) a
where rownum < 16) b
where b.rw > 10;