SQL 分组 查询 次大或 TOP值
有表   TEMP,   列为   a_NAME,   B_NAME，其中a_name为分组列，即有重复字符，B_name为数值，   temp表大于100万行,简如下表：
a_NAME   B_NAME
A   42  
A   333  
B   444  
B   555  
B   666  
C   777  
C   888  
C   999  

查询结果：a,b,c中对应B_name的次大值，或top   6的值（实际中的a,b,c值接近1万   ）



------解决方案--------------------
select
t.a_NAME,max(t.B_NAME)
from
temp t
where
t.B_NAME <(select max(B_NAME) from temp where a_NAME=t.a_NAME)
group by
t.a_NAME

select
t.*
from
temp
where
t.B_NAME in(select top 6 B_NAME from temp where a_NAME=t.a_NAME order by B_NAME desc)
------解决方案--------------------
seelct * from temp a
where (select count(distinct b_name) from temp where a_name=a.a_name and b_name> a.b_name)=1

------解决方案--------------------
--top 6的值

--方法一：
Select * From [TEMP] A
Where (Select Count(*) From [TEMP] Where a_NAME = A.a_NAME And B_NAME > A.B_NAME) < 6
Order By a_NAME, B_NAME

--方法二：
Select * From [TEMP] A
Where Exists (Select Count(*) From [TEMP] Where a_NAME = A.a_NAME And B_NAME > A.B_NAME Having Count(*) < 6)
Order By a_NAME, B_NAME

--方法三：
Select * From [TEMP] A
Where B_NAME In (Select TOP 6 B_NAME From [TEMP] Where a_NAME = A.a_NAME Order By B_NAME Desc)
Order By a_NAME, B_NAME
------解决方案--------------------
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

------解决方案--------------------
--第二大
--方法一：
Select * From [TEMP] A
Where (Select Count(*) From [TEMP] Where a_NAME = A.a_NAME And B_NAME > A.B_NAME) = 1
Order By a_NAME, B_NAME

--方法二：
Select * From [TEMP] A
Where Exists (Select Count(*) From [TEMP] Where a_NAME = A.a_NAME And B_NAME > A.B_NAME Having Count(*) = 1)
Order By a_NAME, B_NAME


--第六大
--方法一：
Select * From [TEMP] A
Where (Select Count(*) From [TEMP] Where a_NAME = A.a_NAME And B_NAME > A.B_NAME) = 5
Order By a_NAME, B_NAME

--方法二：
Select * From [TEMP] A
Where Exists (Select Count(*) From [TEMP] Where a_NAME = A.a_NAME And B_NAME > A.B_NAME Having Count(*) = 5)
Order By a_NAME, B_NAME