请教SQL语句:选择分类排序后的两条记录.
表名: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
------解决方案--------------------
其它方法:
declare @t table(code int, class varchar(10), price int)
insert @t 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
----方法1:
select * from @t as a
where (select count(*) from @t where class = a.class and price > a.price ) < 2
order by class,price DESC
----方法2:
select * from @t as a
where not exists(select 1 from @t where class = a.class and price > a.price group by class having count(*) > 1)
order by class,price DESC
/*结果
code class price
----------- ---------- -----------
7 A 48882
10 A 4122
8 B 417722
5 B 4122
3 C 1543
9 C 854
*/
