Sql分页查询

最新推荐文章于 2023-05-05 15:02:54 发布

Gaulhor Alex

最新推荐文章于 2023-05-05 15:02:54 发布

阅读量687

点赞数

文章标签： sql tsql table

本文链接：https://blog.csdn.net/gaulhor/article/details/2683079

版权

-- 修改了ORDER BY 需要 percent 与 ORDERBY 失效的BUG--
-- -注意'roder by'用一个空格间隔

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 )