if object_id('[userinfo]') is not null drop table [userinfo]
go
create table [userinfo]([userid] int,[username] varchar(4))
insert [userinfo]
select 1,'小王' union all
select 2,'小李'
go
if object_id('[gift]') is not null drop table [gift]
go
create table [gift]([giftid] int,[giftname] varchar(6))
insert [gift]
select 1,'鲜花' union all
select 2,'巧克力' union all
select 3,'飞机' union all
select 4,'航母'
go
if object_id('[sendgift]') is not null drop table [sendgift]
go
create 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 all
select 2,2,1,21,'2012-1-1' union all
select 3,1,2,13,'2012-1-1' union all
select 4,3,2,4,'2012-1-1' union all
select 5,2,1,5,'2012-1-1' union all
select 6,2,1,51,'2012-1-1'
go

with cte as(
select b.giftname,a.userid,a.username,sum(send_num) as SumNum
from sendgift c
join userinfo a on a.userid=c.send_getuserid
join gift b on b.giftid=c.send_giftid
--where 一周内时间条件在这里添加
group by b.giftname,a.userid,a.username
)
select giftname,userid,username,SumNum
from
(
select *,rn=row_number() over(partition by giftname order by sumnum desc) from cte
) t
where rn=1
order 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,