分组取数据

create   table   temp   (   a_NAME   nvarchar(10),   B_NAME     int)
insert   into   temp  
select   'A ',   42  
union   select   'A ',   333  
union   select   'B ',   444  
union   select   'B ',   555  
union   select   'B ',   666  
union   select   'C ',   777  
union   select   'C ',   888  
union   select   'C ',   999  
insert   into   temp   select   'A ',   32
insert   into   temp   select   'A ',   500  
insert   into   temp   select   'A ',   214  
insert   into   temp   select   'A ',   654  
insert   into   temp   select   'A ',   100  
insert   into   temp   select   'A ',   200  


--   top   6  
select   a_NAME,B_NAME   from   temp   a
where   (select   Count(1)   from   temp   b   where     a.a_NAME=b.a_NAME   and   a.B_NAME <b.B_NAME     ) <6
order   by   a_NAME,B_NAME   desc


--次大
select   a_NAME,min(B_NAME)
from(
select   a_NAME,B_NAME   from   temp   a
where   (select   Count(1)   from   temp   b   where     a.a_NAME=b.a_NAME   and   a.B_NAME <b.B_NAME     )=1)   c
group   by   a_NAME