日期:2014-05-18 浏览次数:20603 次
请教什么情况下会用到 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 行受影响) **/