表名: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语句能实现吗?谢谢各位...
表结构:
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语句能实现吗?谢谢各位...
----------- ------------ -----------
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
/*class price
---------- -----------
A 48882
A 4122
B 417722
B 4122
C 1543
C 854(所影响的行数为 6 行)
*/
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
*/