一个关于分组的问题，求助
create table #tab (A varchar(1),B int)
insert into #tab values ( 'a ',1)
insert into #tab values ( 'a ',2)
insert into #tab values ( 'a ',3)
insert into #tab values ( 'b ',1)
insert into #tab values ( 'b ',2)
insert into #tab values ( 'b ',3)
求出 'a ' 和 'b ' 中最大的两个值
需求结果
A B
a 3
a 2
b 3
b 2
[解决办法]
select * from #tab a where b in(select top 2 b from #tab where a=a.a order by b desc)
[解决办法]
select * from #tab tmp
where (select count(*) from #tab where A=tmp.A and B> tmp.B) <2
[解决办法]
select * from #tab t1
where B in(select top 2 B from #tab where A=t1.A order by B desc)
[解决办法]
select a,b
from #tab a
where 2> (select count(1) from #tab where a = a.a and b> a.b)
[解决办法]
select * from #tab tmp
where (select count(1) from #tab where A=tmp.A and B> tmp.B) <2
