笔记10 CROSS APPLY的应用：根据组名取出sales最大值的那个name

笔记10 CROSS APPLY的应用：根据组名取出sales最大值的那个name 

 1 --CROSS APPLY的应用：根据组名取出sales最大值的那个name
 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 *
10 FROM(
11 SELECT DISTINCT  [group]
12 FROM @tb
13 )A
14 CROSS APPLY(
15 SELECT TOP 1 *
16 FROM @tb BB
17 WHERE BB.[group] = A.[group]
18 ORDER BY sales DESC
19 )B
20 
21 
22 
23

 

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