CREATE PROCEDURE dbo.PostGetPostByPage ( @page int, @forumid int, @topornot int ) AS /* SET NOCOUNT ON */ declare @begin int,@end int,@f int,@l int,@count int,@top int select @top=count(*) from Posts as p1 where p1.PostID=(select min(PostID) from Posts as p2 where p1.ThreadID=p2.ThreadID) and p1.ForumID=@forumid and PostType=4 if @topornot=1 select p1.PostType,p1.Title,p1.UserName,p1.TotalViews,p1.PostID,p1.ThreadID,p1.ForumID,FileName, Reply=(select Count(*) from Posts as p2 where p1.ThreadID=p2.ThreadID)-1, LastDate=(select Max(PostDate)from Posts as p2 where p1.ThreadID=p2.ThreadID), LastWriter=(select UserName from posts as p2 where p2.PostID=(select Max(PostID)from Posts as p2 where p1.ThreadID=p2.ThreadID)) from Posts as p1 where p1.PostID=(select min(PostID) from Posts as p2 where p1.ThreadID=p2.ThreadID) and p1.ForumID=@forumid and PostType=4 else if @topornot=2 begin select @count=count(*) from Posts as p1 where p1.PostID=(select min(PostID) from Posts as p2 where p1.ThreadID=p2.ThreadID) and p1.ForumID=@forumid and PostType<>4 declare my_cursor SCROLL CURSOR for select p1.PostID from Posts as p1 where p1.PostID=(select min(PostID) from Posts as p2 where p1.ThreadID=p2.ThreadID) and p1.ForumID=@forumid and PostType<>4 order by (select max(PostID) from Posts as p2 where p1.ThreadID=p2.ThreadID) desc open my_cursor if @count+@top<25 and @page=1 begin select @f=1 select @l=@count end if @count+@top>=25 and @page=1 begin select @f=1 select @l=25-@top end if(@page*25-@top>@count) and @page>1 begin select @f=(@page-1)*25+1-@top select @l=@count end if(@page*25-@top<=@count) and @page>1 begin select @f=(@page-1)*25+1-@top select @l=@page*25-@top end fetch absolute @f from my_cursor into @begin fetch absolute @l from my_cursor into @end set nocount off select p1.PostType,p1.Title,p1.UserName,p1.TotalViews,p1.PostID,p1.ThreadID,p1.ForumID,FileName, Reply=(select Count(*) from Posts as p2 where p1.ThreadID=p2.ThreadID)-1, LastDate=(select Max(PostDate)from Posts as p2 where p1.ThreadID=p2.ThreadID), LastWriter=(select UserName from posts as p2 where p2.PostID=(select Max(PostID)from Posts as p2 where p1.ThreadID=p2.ThreadID)) from Posts as p1 where p1.PostID=(select min(PostID) from Posts as p2 where p1.ThreadID=p2.ThreadID) and p1.ForumID=@forumid and PostID<=@begin and PostID>=@end and PostType<>4 order by (select max(PostID) from Posts as p2 where p1.ThreadID=p2.ThreadID) desc close my_cursor end RETURN @@Rowcount GO ---------- 以下为一数据内容: 字段: 员工卡号(nvarchar) 打卡日期(smalldatetime) 打卡时间(smalldatetime) 687623 2004-5-26 2004-5-26 7:29:00 687623 2004-5-26 2004-5-26 11:5:00 687623 2004-5-26 2004-5-26 13:31:00 687623 2004-5-26 2004-5-26 17:33:00 687244 2004-5-26 2004-5-26 7:35:00 687244 2004-5-26 2004-5-26 11:1:00 687244 2004-5-26 2004-5-26 13:28:00 687244 2004-5-26 2004-5-26 17:24:00 : : : : : : : : : 需达到以下效果: 员工卡号 打卡日期 打卡明细时间 687623 2004-5-26 7:29 11:5 13:31 17:33 687244 2004-5-26 7:35 11:1 13:28 17:24 其中, 打卡明细时间由上表的打卡时间而来 --创建一个合并处理函数(生成打卡明细时间) create function f_time( @员工卡号 nvarchar(6), @打卡日期 smalldatetime )returns varchar(8000) as begin declare @re varchar(8000) set @re='' select @re=@re+' '+convert(char(5),打卡时间,108) from 数据表 where 员工卡号=@员工卡号 and 打卡日期=@打卡日期 return(stuff(@re,1,1,'')) end go --调用函数实现查询 select 员工卡号,打卡日期,打卡明细时间=dbo.f_time(员工卡号,打卡日期) from 数据表 group by 员工卡号,打卡日期 |