select Type, max(Price) form Sp group by Type order by Type
create table test(pid int primary key identity(1,1),category varchar(20),price int) insert into test(category,price)values('A',6) insert into test(category,price)values('A',4) insert into test(category,price)values('A',2) insert into test(category,price)values('B',6) insert into test(category,price)values('B',5) insert into test(category,price)values('C',6) insert into test(category,price)values('C',3)select pid,category,price from test t where pid in(select top 1 pid from test where t.category=category )
select * from Sp where Type+Price in ( select Type+max(Price) from Sp group by Type ) order by Type
from Sp
group by 种类
form Sp
group by Type
order by Type
insert into test(category,price)values('A',6)
insert into test(category,price)values('A',4)
insert into test(category,price)values('A',2)
insert into test(category,price)values('B',6)
insert into test(category,price)values('B',5)
insert into test(category,price)values('C',6)
insert into test(category,price)values('C',3)select pid,category,price from test t where pid in(select top 1 pid from test where t.category=category )
from Sp
where Type+Price in
(
select Type+max(Price)
from Sp
group by Type
)
order by Type
from Sp
where [Type]+CAST(Price AS CHAR) in
(
select [Type]+CAST(max(Price) AS CHAR)
from Sp
group by [Type]
)
order by [Type]
from Sp
where [Type]+CAST(Price AS CHAR) in
(
select [Type]+CAST(max(Price) AS CHAR)
from Sp
group by [Type]
)
order by [Type]
select pid,category,price from test t where pid in(select top 1 pid from test where t.category=category order by price desc )
select *
from Sp t
where not exists(select * from Sp where 种类=t.种类 and 价格>t.价格)
select sp.* from sp join
(select kind,max(price) pri from sp group by kind) b
on sp.kind=b.kind and sp.price=b.prikind为种类,price为价格
from Sp t
where not exists(select * from Sp where 种类=t.种类 and 价格>t.价格)