一个关于分组的问题，求助

[kk19840210]

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

[chuifengde]

select * from #tab a where b in(select top 2 b from #tab where a=a.a order by b desc)

[marco08]

select * from #tab tmp
where (select count(*) from #tab where A=tmp.A and B>tmp.B)<2

[marco08]

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)

select * from #tab tmp
where (select count(*) from #tab where A=tmp.A and B>tmp.B)<2
order by A, B desc

--result
A B
---- -----------
a 3
a 2
b 3
b 2

[rookie_one]

select * from #tab t1
where B in(select top 2 B from #tab where A=t1.A order by B desc)

[leo_lesley]

select a,b
from #tab a
where 2>(select count(1) from #tab where a = a.a and b>a.b)

[hrb2008]

select * from #tab tmp
where (select count(1) from #tab where A=tmp.A and B>tmp.B)<2

[kk19840210]

2

select * from #tmp a where (select count(1) from #tmp where type=a.type and grade>a.grade)<2 order by [type]

name       type       grade
---------- ---------- ----------
A          com        98
B          com        82
C          eng        82
A          eng        92
A          math       85

(5 行受影响)

[prcgolf]

select a,b from (select *,row_number()over(partition by a order by b desc) as x from #tab) y
where x<=2 order by a,x