100分求一条SQL,是在搞不定了解决办法

100分求一条SQL,是在搞不定了
先有3个表,用户表/礼物表/礼物赠送记录表

userinfo用户表字段如下:
-------------------
userid username
-------------------
  1 小王
  2 小李
-------------------

gift礼物表字段如下:
-------------------
giftid giftname  
-------------------
  1 鲜花
  2 巧克力
  3 飞机
  4 航母
-------------------

sendgift礼物赠送记录表字段如下:
-----------------------------------------------------------
sendid send_giftid(礼物id) send_getuserid(接收人用户id) send_num(礼物数量) send_time
-----------------------------------------------------------
  1 4 2 11 2012-1-1
  2 2 1 21 2012-1-1
  3 1 2 13 2012-1-1
  4 3 2 4 2012-1-1
  5 2 1 5 2012-1-1
  6 2 1 51 2012-1-1
-----------------------------------------------------------


要求如下:

查询 [每个] 礼物 [一周] 内接收 [总数量] 最多的用户

如:
---------------------------------------------
  (giftname)礼物名称 (userid)用户ID (username)用户昵称 (SumNum)总数量
---------------------------------------------
  鲜花 2 小李 77
  巧克力 1 小王 13
  ... ... ... ...
---------------------------------------------




[解决办法]

SQL code

if object_id('[userinfo]') is not null drop table [userinfo]gocreate table [userinfo]([userid] int,[username] varchar(4))insert [userinfo]select 1,'小王' union allselect 2,'小李'goif object_id('[gift]') is not null drop table [gift]gocreate table [gift]([giftid] int,[giftname] varchar(6))insert [gift]select 1,'鲜花' union allselect 2,'巧克力' union allselect 3,'飞机' union allselect 4,'航母'goif object_id('[sendgift]') is not null drop table [sendgift]gocreate table [sendgift]([sendid] int,[send_giftid] int,[send_getuserid] int,[send_num] int,[send_time] datetime)insert [sendgift]select 1,4,2,11,'2012-1-1' union allselect 2,2,1,21,'2012-1-1' union allselect 3,1,2,13,'2012-1-1' union allselect 4,3,2,4,'2012-1-1' union allselect 5,2,1,5,'2012-1-1' union allselect 6,2,1,51,'2012-1-1'gowith cte as(select b.giftname,a.userid,a.username,sum(send_num) as SumNumfrom sendgift cjoin userinfo a on a.userid=c.send_getuseridjoin gift b on b.giftid=c.send_giftid--where 一周内时间条件在这里添加group by b.giftname,a.userid,a.username)select giftname,userid,username,SumNumfrom(select *,rn=row_number() over(partition by giftname order by sumnum desc) from cte) twhere rn=1order by SumNum desc/**giftname userid      username SumNum-------- ----------- -------- -----------巧克力      1           小王       77鲜花       2           小李       13航母       2           小李       11飞机       2           小李       4(4 行受影响)**/
[解决办法]
SQL code
select gift.giftname as 礼物名称,userinfo.userid as 用户ID,userinfo.username as 用户昵称,topinfo.send_count as 总数量from (    select *    from    (        select row_number() over(partition by send_giftid order by send_giftid,sum(send_num) desc) as topShow, send_giftid,send_getuserid,sum(send_num) as send_count        from sendgift        where DATEDIFF(D,send_time,GETDATE())<7         group by send_giftid,send_getuserid    ) as oneWeekInfo    where topShow=1) as topInfoinner join gift on gift.giftid=topInfo.send_giftidinner join userinfo on userinfo.userid=topInfo.send_getuseridorder by gift.giftname