27,579
社区成员
发帖
与我相关
我的任务
分享
请教什么情况下会用到 with as ? 先谢谢了!
比如以下这个例子,为什么会用到with as
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 行受影响)
**/