Sql分页查询

最新推荐文章于 2024-11-22 10:11:53 发布

原创最新推荐文章于 2024-11-22 10:11:53 发布 · 790 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sql #tsql #table

本文介绍了一个SQL存储过程，用于实现数据库查询的分页功能，并修复了原有ORDER BY语句中存在的百分比排序问题。该过程能够根据用户提供的页码、每页条数及原始SQL语句来返回指定范围的数据记录。

--修改了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)