sql 2005 分页排序问题

[geass]

有 user,message两个表
结果如下
user
pkid int

message
pkid int
userid int
Titles nvarchar(50)
CreateTime datetime

要写个 存储过程，输入起始时间和结束时间，统计出每个user在这个时间段的 message 数量 并按数量分页排序，

[喜-喜]

最佳利用存储过程实现分页的程序

[htl258_Tony]

--有 user,message两个表
--结果如下
--user
--pkid int
--name 应该还有个字段

--message
--pkid int
--userid int
--Titles nvarchar(50)
--CreateTime datetime

--要写个 存储过程，输入起始时间和结束时间，统计出每个user在这个时间段的 message 数量 并按数量分页排序

IF OBJECT_ID('[p_test]') IS NOT NULL
    DROP PROC [p_test]
GO
CREATE PROC [p_test]
@bt datetime,
@et datetime,
@PageCurrent int=1,--显示第几页
@PageSize int=10 --每页大小
AS
WITH T AS
(
	SELECT Rn=row_number()OVER(ORDER BY count(1) DESC), 
		A.name,count(1) cnt 
	FROM [User] A
		JOIN [message] B 
			ON A.pkid=B.pkid
	GROUP BY A.name
)
SELECT * 
FROM T 
WHERE (Rn-1)/@PageSize+1=@PageCurrent
GO

[黄_瓜]

declare @begintime datetime, @endtime datetime
;with t as
(
select count(*) as num ,u.username from user u,message m
where u.pid=m.userid 
and CreateTime between @begintime and @endtime
group by u.username
)
,b
as
(
select id=row_number() over(order by num),t.* from t
)
select * from b where id between 0 and 10 

[黄_瓜]

declare @begintime datetime, @endtime datetime,@pageCurrent int,@pageSize int
set @pageCurrent =1,--显示第几页
set @pageSize =10 --每页大小

;with t as
(
select count(*) as num ,u.username from user u,message m
where u.pid=m.userid 
and CreateTime between @begintime and @endtime
group by u.username
)
,b
as
(
select id=row_number() over(order by num),t.* from t
)
select * from b where (is-1)/@pageSize+1=@pageCurrent

[htl258_Tony]

[Quote=引用 2 楼 htl258 的回复:]
SQL code
--有 user,message两个表
--结果如下
--user
--pkid int
--name 应该还有个字段

--message
--pkid int
--userid int
--Titles nvarchar(50)
--CreateTime datetime

--要写个 存储过程，输入起始时间和结束时间，统计出每个user在这个时间段……
[/Quote]modify

--有 user,message两个表
--结果如下
--user
--pkid int
--name 应该还有个字段

--message
--pkid int
--userid int
--Titles nvarchar(50)
--CreateTime datetime

--要写个 存储过程，输入起始时间和结束时间，统计出每个user在这个时间段的 message 数量 并按数量分页排序

IF OBJECT_ID('[p_test]') IS NOT NULL
    DROP PROC [p_test]
GO
CREATE PROC [p_test]
@bt datetime,
@et datetime,
@PageCurrent int=1,--显示第几页
@PageSize int=10 --每页大小
AS
WITH T AS
(
	SELECT Rn=row_number()OVER(ORDER BY count(1) DESC), 
		A.name,count(1) cnt 
	FROM [User] A
		JOIN [message] B 
			ON A.pkid=B.pkid
	WHERE B.CreateTime BETWEEN @bt AND @et
	GROUP BY A.name
)
SELECT * 
FROM T 
WHERE (Rn-1)/@PageSize+1=@PageCurrent
GO

[geass]

先感谢上面回复了问题的高手。如果结果符合，马上结贴。

[geass]

一开始我没有把问题说清楚。
要把所有 user 表的数据都列出来，如果对应 message 表没有记录，用0表示

刚才 试了 5楼 htl258 的方法。
我把 5楼方法改了一下

WITH T AS
(
    SELECT Rn=row_number()OVER(ORDER BY count(1) DESC), 
        A.name,count(1) cnt 
    FROM [User] A
       left JOIN [message] B 
            ON A.pkid=B.pkid
    and B.CreateTime BETWEEN @bt AND @et
    GROUP BY A.name
)
SELECT * 
FROM T 
WHERE (Rn-1)/@PageSize+1=@PageCurrent

结果 user 表那些 本来 message 数量为0的都变成了1
不知道应该怎样修改

[永生天地]

WITH T AS
(
    SELECT Rn=row_number()OVER(ORDER BY count(1) DESC), 
        A.name,count(1) cnt 
    FROM [User] A
       left JOIN [message] B 
            ON A.pkid=B.userid--B 表怎么还用pkid ，应该用userid
    and B.CreateTime BETWEEN @bt AND @et
    GROUP BY A.name
)
SELECT * 
FROM T 
WHERE (Rn-1)/@PageSize+1=@PageCurrent

[htl258_Tony]

WITH T AS
(
    SELECT Rn=row_number()OVER(ORDER BY count(1) DESC), 
        A.name,count(b.pkid) cnt 
    FROM [User] A
       left JOIN [message] B 
            ON A.pkid=B.pkid
    and B.CreateTime BETWEEN @bt AND @et
    GROUP BY A.name
)
SELECT * 
FROM T 
WHERE (Rn-1)/@PageSize+1=@PageCurrent