求特殊分页存储过程(期待高手解惑答疑)

Question

--这种情况难处理了(主要是分页没有效率)--示例
create table tb(ID int identity,grade varchar(10),uptime datetime)
set identity_insert tb on
insert tb(id,grade,uptime)
          select 1 ,'a','2004-12-11'
union all select 2 ,'b','2004-12-11'
union all select 3 ,'c','2004-12-11'
union all select 4 ,'a','2004-12-12'
union all select 5 ,'c','2004-12-13'
union all select 6 ,'c','2004-12-13'
union all select 7 ,'a','2004-12-14'
union all select 8 ,'a','2004-12-15'
union all select 9 ,'b','2004-12-16'
union all select 10,'b','2004-12-17'
union all select 11,'a','2004-12-17'
set identity_insert tb off
go--分页处理的存储过程
create proc p_split
@currentpage int=1,  --要显示的当前页
@pagesize int=5      --每页的大小
as
set nocount on
set @currentpage=@currentpage*@pagesize
set rowcount @currentpage
select * into #t from tb a
order by ((select count(*) from tb where grade=a.grade and(uptime>a.uptime or uptime=a.uptime and id>=a.id))-1)
/case grade when 'c' then 2 when 'b' then 1 when 'a' then 2 end
,case grade when 'c' then 1 when 'b' then 2 when 'a' then 3 end,id desc
if @currentpage>@pagesize
begin
set @currentpage=@currentpage-@pagesize
set rowcount @currentpage
delete from #t
end
select * from #t
go--调用
exec p_split 1
exec p_split 2
exec p_split 3
go--删除测试
drop table tb
drop proc p_split/*--测试结果ID          grade      uptime  
----------- ---------- -------------------------
6           c          2004-12-13 00:00:00.000
5           c          2004-12-13 00:00:00.000
10          b          2004-12-17 00:00:00.000
11          a          2004-12-17 00:00:00.000
8           a          2004-12-15 00:00:00.000
ID          grade      uptime  
----------- ---------- -------------------------
3           c          2004-12-11 00:00:00.000
9           b          2004-12-16 00:00:00.000
7           a          2004-12-14 00:00:00.000
4           a          2004-12-12 00:00:00.000
2           b          2004-12-11 00:00:00.000
ID          grade      uptime  
----------- ---------- -------------------------
1           a          2004-12-11 00:00:00.000
--*/

Answer 1

如果更改了每页的大小,则存储过程中,排序的处理也要做相应的修改,即:
case grade when 'c' then 2 when 'b' then 1 when 'a' then 2 end
部分,这个控制每类/每页多少条记录

Answer 2

看看这个：
一个将数据分页的存储过程     
 
 CREATE PROCEDURE sp_page
  @tb         varchar(50), --表名
  @col        varchar(50), --按该列来进行分页
  @coltype    int,         --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
  @orderby    bit,         --排序,0-顺序,1-倒序
  @collist    varchar(800),--要查询出的字段列表,*表示全部字段
  @pagesize   int,         --每页记录数
  @page       int,         --指定页
  @condition  varchar(800),--查询条件
  @pages      int OUTPUT   --总页数
ASDECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
  SET @where1=' WHERE '
  SET @where2='  '
END
ELSE
BEGIN--有查询条件
  SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
  SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
         ') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数
IF @orderby=0
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
           ' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
           ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
           @col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
           ' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
           ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
           @col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
           @col+' DESC'
IF @page=1--第一页
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
    @where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
GO
 

Answer 3

一个将数据分页的存储过程     选择自 pbsql 的 Blog  
关键字   一个将数据分页的存储过程 
出处    
 
 CREATE PROCEDURE sp_page
  @tb         varchar(50), --表名
  @col        varchar(50), --按该列来进行分页
  @coltype    int,         --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
  @orderby    bit,         --排序,0-顺序,1-倒序
  @collist    varchar(800),--要查询出的字段列表,*表示全部字段
  @pagesize   int,         --每页记录数
  @page       int,         --指定页
  @condition  varchar(800),--查询条件
  @pages      int OUTPUT   --总页数
AS
/*
功能描述：对指定表中满足条件的记录按指定列进行分页查询，分页可以顺序、倒序
         查询可以指定页大小、指定查询任意页、指定输出字段列表，返回总页数
作    者：pbsql
版    本：1.10
最后修改：2004-11-29
*/
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
  SET @where1=' WHERE '
  SET @where2='  '
END
ELSE
BEGIN--有查询条件
  SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
  SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
         ') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数
IF @orderby=0
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
           ' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
           ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
           @col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
           ' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
           ' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
           @col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
           @col+' DESC'
IF @page=1--第一页
  SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
    @where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
GO本存储过程高效，曾用500万条数据测试（已建索引），只返回分页只需3秒，影响效率的地方是计算总页数，若不需要可以注释掉--测试示例
declare @pages int
select identity(int,1,1) id,getdate() dt,xx=cast('' as varchar(10)) into #t
 from sysobjects
update #t set dt=dateadd(day,id-200,dt),
              xx='xxxx'+right('000000'+cast(id as varchar(10)),6)exec sp_page '#t','id',0,0,'*',10,2,'',@pages output--按id顺序取第二页
exec sp_page '#t','id',0,1,'*',10,2,'',@pages output--按id倒序取第二页
exec sp_page '#t','xx',1,0,'*',10,3,'',@pages output--按xx顺序取第三页
exec sp_page '#t','xx',1,1,'*',10,3,'',@pages output--按xx倒序取第三页
exec sp_page '#t','dt',2,0,'*',10,2,'',@pages output--按dt顺序取第二页
exec sp_page '#t','dt',2,1,'*',10,2,'',@pages output--按dt倒序取第二页select 总页数=@pagesdrop table #t

Answer 4

拜邹老大...我先去试试
同时谢谢goregrypeck(派克) long0104() 那个分页存储过程都是pbsql 写的通用存储过程。

Answer 5

case grade when 'c' then 1 when 'b' then 2 when 'a' then 3  --> 为什么是3不是2呢？
delete from #t ---> 先delete
select * from #t -->后select 也可以？刚接触ms sql时间还不太长请邹老大指点一下。

Answer 6

create table [table]
(id int,grade varchar(1),uptime datetime)
go
insert into [table] select 
1,         'a'          ,   '2004-12-11' union all select
2 ,        'b'         ,    '2004-12-11' union all select
3  ,       'c'        ,     '2004-12-11' union all select
4   ,      'a'       ,      '2004-12-12' union all select
5    ,     'c'      ,       '2004-12-13' union all select
6     ,    'c'     ,        '2004-12-13' union all select
7      ,   'a'    ,         '2004-12-14' union all select
8       ,  'a'   ,          '2004-12-15' union all select
9        , 'b'  ,           '2004-12-16' union all select
10        ,'b' ,            '2004-12-17' union all select
11        ,'a',             '2004-12-17'
create procedure d_test
@page int
as 
select sn=identity(int,1,1),* into #t from 
(select top 100 percent * from (
select id,grade,uptime,
        upgrade=(select count(*) from (select distinct grade,uptime from [table]) 
b where b.uptime>=a.uptime and grade=a.grade) 
from [table] a) aa
order by upgrade,charindex(grade,'cba')+upgrade/10,id desc) ab
select * from #t where sn>(@page-1)*5 and sn<=@page*5

Answer 7

感谢zheninchangjiang(我爱燕子) 做的测试虽然理解错了

Answer 8

case grade when 'c' then 1 when 'b' then 2 when 'a' then 3 你的类别顺序是c>b>a嘛,当然是1,2,3至于#t的问题,因为我是用存储过程,所以#t会随存储过程自动删除,你不用管它(多个用户同时使用也不会有冲突,关于临时表,看联机帮助)

Answer 9

对邹老大给出的存储过程有几个疑问1) case grade when 'c' then 1 when 'b' then 2 when 'a' then 3  --> 为什么是3不是2呢？
2) delete from #t ---> 先delete
   select * from #t -->后select 也可以？
3) 如果更改了每页的大小,则存储过程中,排序的处理也要做相应的修改,即:
   case grade when 'c' then 2 when 'b' then 1 when 'a' then 2 end
   ----如果每页大小比较大那就会出现多种case吧？
4) 测试exec p_split 2/*--测试结果
ID          grade      uptime  
----------- ---------- -------------------------
3           c          2004-12-11 00:00:00.000
9           b          2004-12-16 00:00:00.000
7           a          2004-12-14 00:00:00.000
4           a          2004-12-12 00:00:00.000
2           b          2004-12-11 00:00:00.000这个最后一条记录应该排到第三条
刚接触ms sql时间还不太长请邹老大指点一下。

Answer 10

因为上面的c只有一条,所以按顺序补了一条b上去,而
2           b          2004-12-11 00:00:00.000
是第二次不足时补上去的,所以排在第一批补数据的后面

Answer 11

--如果要实现楼主后面的那种排序,只需要在输出结果时,再次排序就行了.--分页处理的存储过程
create proc p_split
@currentpage int=1,  --要显示的当前页
@pagesize int=5      --每页的大小
as
set nocount on
set @currentpage=@currentpage*@pagesize
set rowcount @currentpage
select *
into #t from tb a
order by ((select count(*) from tb where grade=a.grade and(uptime>a.uptime or uptime=a.uptime and id>=a.id))-1)
/case grade when 'c' then 2 when 'b' then 1 when 'a' then 2 end
,case grade when 'c' then 1 when 'b' then 2 when 'a' then 3 end,id desc
if @currentpage>@pagesize
begin
set @currentpage=@currentpage-@pagesize
set rowcount @currentpage
delete from #t
end
select * from #t 
order by case grade when 'c' then 1 when 'b' then 2 when 'a' then 3 end
,uptime desc,id desc
go

Answer 12

是这是我昨天糊涂了把这事忘了。昨天机器坏了没法回复/结贴。谢谢邹老大热心帮忙。
同时感谢两位给通过存储过程的朋友和 zheninchangjiang(我爱燕子)。