sql 2005 ·ÖÒ³ÅÅÐòÎÊÌâ - MS-SQL Server / ÒÉÄÑÎÊÌâ

ÓÐ user,messageÁ½¸ö±í
½á¹ûÈçÏÂ
user
pkid int

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

Ҫд¸ö ´æ´¢¹ý³Ì£¬ÊäÈëÆðʼʱ¼äºÍ½áÊøʱ¼ä£¬Í³¼Æ³öÿ¸öuserÔÚÕâ¸öʱ¼ä¶ÎµÄ message ÊýÁ¿ ²¢°´ÊýÁ¿·ÖÒ³ÅÅÐò£¬

---

×î¼ÑÀûÓô洢¹ý³ÌʵÏÖ·ÖÒ³µÄ³ÌÐò

---

SQL code:
--ÓÐ 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

---

SQL code:
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