jeRKer

问题如下：表tb:
name  group   sales
--------------------------------
aa       G1        3
bb       G1        5
cc        G2        4
dd       G2        3
想取每个group中sales最大的name。不用子查询，请教怎么查？

思路：在SQLServer 2005后我们可以用over开窗函数来代替子查询实现来提高效率,over子句除了排名函数之外也可以和聚合函数配合。
方法一：

declare @tb table([name] varchar(2),[group] varchar(2),[sales] int)
insert @tb
select 'aa','G1',3 union all
select 'bb','G1',5 union all
select 'cc','G2',4 union all
select 'dd','G2',3

;with maco as
(
 select *,
 row_number() over (partition by [group] order by [sales] desc) as id
 from @tb
)
select name,[group],sales from maco where id=1

思路2：

declare @tb table([name] varchar(2),[group] varchar(2),[sales] int)
insert @tb
select 'aa','G1',3 union all
select 'bb','G1',5 union all
select 'cc','G2',4 union all
select 'dd','G2',3

select *
from
(
select *,max([sales]) over(partition by [group] ) as max_sales_group from @tb
) as a
where a.[max_sales_group]=a.[sales]

思路3：也可以利用CROSS APPLY用法

 SELECT *FROM(SELECT DSITINCT [group]FROM tb)A

CROSS APPLY(SELECT TOP 1 *FROM tb BB WHERE BB.[group] = A.[group]ORDER BY sales DESC)B

结果如下：

name  group sales
bb       G1        5  
cc        G2        4