请教什么情况下会用到 with as
请教什么情况下会用到 with as ? 先谢谢了！比如以下这个例子，为什么会用到with as 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 行受影响)**/