笔记9 over开窗函数实现 cross apply的功能

笔记9 over开窗函数实现 cross apply的功能  

 1 --over开窗函数实现 cross apply的功能  
 2 declare @tb table([name] varchar(2),[group] varchar(2),[sales] int)
 3 insert @tb
 4 select 'aa','G1',3 union all
 5 select 'bb','G1',5 union all
 6 select 'cc','G2',4 union all
 7 select 'dd','G2',3
 8 
 9 --SELECT * FROM @tb
10 select name,[group] ,sales
11 from
12 (
13 select *,max([sales]) over(partition by [group] ) as max_sales_group from @tb
14 ) as a
15 where a.[max_sales_group]=a.[sales] 

 

posted @ 2013-08-01 22:36  桦仔  阅读(478)  评论(0编辑  收藏  举报