Create PROCEDURE [dbo].[les_AllowPaging]
@pageindex int, ----*****页码
@PageSize int, ----*****每页显示条数
@tsql varchar(4000)----*****SQL语句
as
Declare @SqlSelect varchar(4000)
Declare @orderby varchar(4000)
Declare @AllowPagingSql varchar(4000)
---判断是否排序
if CHARINDEX('order by',@tsql) <> 0
begin
set @SqlSelect=replace(substring (@tsql,1, CHARINDEX('order by',@tsql)-1),'$','''')
set @orderby=replace(substring (@tsql, CHARINDEX('order by',@tsql),len(@tsql) ),'$','''')
set @AllowPagingSql=
'select * from (SELECT ROW_NUMBER() OVER('+@orderby+') AS AllowPagingId,* FROM ('+
@SqlSelect
+') as table1) as table2 where AllowPagingId between '
+convert(varchar(10),((@pageindex-1) * @PageSize+1))+' and '
+convert(varchar(10), @pageindex * @PageSize)
exec (@AllowPagingSql)
end
else
begin
set @SqlSelect=replace(@tsql,'$','''')
set @orderby=''
set @AllowPagingSql=
'select * from (SELECT *,ROW_NUMBER() OVER(ORDER BY orderbyID DESC) AS AllowPagingId FROM ( select *, 1 as orderbyID from ( '
+@SqlSelect
+' ) as tbs1 ) as Tabl1 ) as table2 where AllowPagingId between '
+convert(varchar(10),((@pageindex-1) * @PageSize+1))+' and '
+convert(varchar(10), @pageindex * @PageSize)
exec (@AllowPagingSql)
end
set @AllowPagingSql='select
case
when count(*)%'+convert(varchar(10),@PageSize)+'=0 then count(*)/'+convert(varchar(10),@PageSize)+'
when count(*)%'+convert(varchar(10),@PageSize)+'<>0 then count(*)/'+convert(varchar(10),@PageSize)+'+1
end as pageCount,count(*) as RowsCount from ('+@SqlSelect+') as tab1'
exec (@AllowPagingSql)
--(利用Not In和SELECT TOP分页)
SELECT TOP 10 *
FROM tbl_message
WHERE(ID NOT IN(SELECT TOP 0 id FROM tbl_message ORDER BY id))
ORDER BY ID
--分页方案二:(利用ID大于多少和SELECT TOP分页)
SELECT TOP 10 * FROM tbl_message
WHERE
(ID>(SELECT MAX(id) FROM
(SELECT TOP 1 id FROM tbl_message ORDER BY id)
AST)
) ORDER BY ID
SELECTTOP页大小*
FROMTestTable
WHERE(ID>
(SELECTMAX(id)
FROM(SELECTTOP页大小*页数id
FROM表
ORDERBYid)AST))
ORDERBYID
--3.分页方案三:(利用SQL的游标存储过程分页)
create procedure SqlPager
@sqlstr nvarchar(4000),--查询字符串
@currentpage int,--第N页
@pagesize int--每页行数
as
set nocount on
--P1是游标的id
declare @P1 int,@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select
ceiling(1.0*@rowcount/@pagesize)
as '总页数',@rowcount
as '总行数',@currentpage
as '当前页'
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
--其它的方案:如果没有主键,可以用临时表,也可以用方案三做,但是效率会低。
-- 建议优化的时候,加上主键和索引,查询效率会提高。
-- 通过SQL 查询分析器,显示比较:我的结论是:
-- 分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句
-- 分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句
-- 分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用
@pageindex int, ----*****页码
@PageSize int, ----*****每页显示条数
@tsql varchar(4000)----*****SQL语句
as
Declare @SqlSelect varchar(4000)
Declare @orderby varchar(4000)
Declare @AllowPagingSql varchar(4000)
---判断是否排序
if CHARINDEX('order by',@tsql) <> 0
begin
set @SqlSelect=replace(substring (@tsql,1, CHARINDEX('order by',@tsql)-1),'$','''')
set @orderby=replace(substring (@tsql, CHARINDEX('order by',@tsql),len(@tsql) ),'$','''')
set @AllowPagingSql=
'select * from (SELECT ROW_NUMBER() OVER('+@orderby+') AS AllowPagingId,* FROM ('+
@SqlSelect
+') as table1) as table2 where AllowPagingId between '
+convert(varchar(10),((@pageindex-1) * @PageSize+1))+' and '
+convert(varchar(10), @pageindex * @PageSize)
exec (@AllowPagingSql)
end
else
begin
set @SqlSelect=replace(@tsql,'$','''')
set @orderby=''
set @AllowPagingSql=
'select * from (SELECT *,ROW_NUMBER() OVER(ORDER BY orderbyID DESC) AS AllowPagingId FROM ( select *, 1 as orderbyID from ( '
+@SqlSelect
+' ) as tbs1 ) as Tabl1 ) as table2 where AllowPagingId between '
+convert(varchar(10),((@pageindex-1) * @PageSize+1))+' and '
+convert(varchar(10), @pageindex * @PageSize)
exec (@AllowPagingSql)
end
set @AllowPagingSql='select
case
when count(*)%'+convert(varchar(10),@PageSize)+'=0 then count(*)/'+convert(varchar(10),@PageSize)+'
when count(*)%'+convert(varchar(10),@PageSize)+'<>0 then count(*)/'+convert(varchar(10),@PageSize)+'+1
end as pageCount,count(*) as RowsCount from ('+@SqlSelect+') as tab1'
exec (@AllowPagingSql)
--(利用Not In和SELECT TOP分页)
SELECT TOP 10 *
FROM tbl_message
WHERE(ID NOT IN(SELECT TOP 0 id FROM tbl_message ORDER BY id))
ORDER BY ID
--分页方案二:(利用ID大于多少和SELECT TOP分页)
SELECT TOP 10 * FROM tbl_message
WHERE
(ID>(SELECT MAX(id) FROM
(SELECT TOP 1 id FROM tbl_message ORDER BY id)
AST)
) ORDER BY ID
SELECTTOP页大小*
FROMTestTable
WHERE(ID>
(SELECTMAX(id)
FROM(SELECTTOP页大小*页数id
FROM表
ORDERBYid)AST))
ORDERBYID
--3.分页方案三:(利用SQL的游标存储过程分页)
create procedure SqlPager
@sqlstr nvarchar(4000),--查询字符串
@currentpage int,--第N页
@pagesize int--每页行数
as
set nocount on
--P1是游标的id
declare @P1 int,@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select
ceiling(1.0*@rowcount/@pagesize)
as '总页数',@rowcount
as '总行数',@currentpage
as '当前页'
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
--其它的方案:如果没有主键,可以用临时表,也可以用方案三做,但是效率会低。
-- 建议优化的时候,加上主键和索引,查询效率会提高。
-- 通过SQL 查询分析器,显示比较:我的结论是:
-- 分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高,需要拼接SQL语句
-- 分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句
-- 分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用