CTE with as用法

参考资料：http://www.cnblogs.com/fygh/archive/2011/08/31/2160266.html

                 http://blog.csdn.net/jacky4955/article/details/5888262

                 http://www.cnblogs.com/CareySon/archive/2011/12/12/2284740.html

 select sum(Cost) as costs,isType,remark from student where isType=1 group by remark,isType
 union all
 select sum(Cost) as costs,isType,remark from student where isType=0 group by remark,isType



 select * from [dbo].[Score] where studentid in (select ID from [Student] where Name='小明')

 declare @t table(CurrenCode nvarchar(20)) 
 insert into @t(CurrenCode)(select ID from [Student] where Name='小明')
 select * from [Score] where studentid in (select * from @t)

 with
 cr as
 (
   select ID from Student where Name='小明'
 )
 select * from Score where studentID in (select * from cr)