＋设为首页 ＋添加到收藏夹
表中有三个列
PKID int 自动加1
tname varchar(20)
price money
....
tname 可重复
pkid tname price ....
1 aa 30
2 bb 40
3 cc 60
4 bb 30
5 cc 70
6 aa 50
我想要的结果是:
pkid tname price ....
6 aa 50
2 bb 40
5 cc 70
如何实现??
select * from table1 where price in (select max(price) from table1 group by tname)
--生成测试数据
create table #t(pkid int,tname char(2),price int)
insert into #t select 1,aa,30
insert into #t select 2,bb,40
insert into #t select 3,cc,60
insert into #t select 4,bb,30
insert into #t select 5,cc,70
insert into #t select 6,aa,50
--执行查询
select
a.*
from
#t a
where
a.pkid = (select top 1 pkid from #t where tname=a.tname order by price desc)
--输出结果
pkid tname price
---- ----- -----
6 aa 50
2 bb 40
5 cc 70
select *
from 表 t
where not exists(select 1 from 表 where tname=t.tname and price>t.price)
