表名:tt
表结构:
code class price
----------- ------------ -----------
1 A 123
2 B 1563
3 C 1543
4 A 456
5 B 4122
6 C 411
7 A 48882
8 B 417722
9 C 854
10 A 4122
要求结果:
class price
------------ -----------
A 48882
A 4122
B 417722
B 4122
C 1543
C 854
只用一条SQL语句能实现吗?谢谢各位...
------解决方案--------------------
declare @a table(code int, class varchar(10), price int)
----------- ------------ -----------
insert @a select 1 , 'A ', 123
union all select 2 , 'B ', 1563
union all select 3 , 'C ', 1543
union all select 4 , 'A ', 456
union all select 5 , 'B ', 4122
union all select 6 , 'C ', 411
union all select 7 , 'A ', 48882
union all select 8 , 'B ',417722
union all select 9 , 'C ', 854
union all select 10 , 'A ', 4122
select class,price from @a a where code in(select top 2 code from @a where class=a.class order by price desc)
order by class
------解决方案--------------------