SQL 语法求助 ，高手赐教

[临窗飞飞]

要求如下

table item price comments
A 8 3号买的
B 2 10号买的
A 3 15号买的
B 6 17号买的
B 9 20号买的
A 1 30号买的要求输出A,B两个 PRICE最大的，并顺带出COMMENTS

输出结果 item price comments
A 8 3号买的
B 9 20号买的

[--小F--]

select * from tb t where price=(select max(price) from tb where iteim=t.item)

[-晴天]

select * from tb a where not exists(select 1 from tb where item=a.item and price>a.price)

[Mr_Nice]

if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (item nvarchar(2),price int,comments nvarchar(10))
insert into [TB]
select 'A',8,'3号买的' union all
select 'B',2,'10号买的' union all
select 'A',3,'15号买的' union all
select 'B',6,'17号买的' union all
select 'B',9,'20号买的' union all
select 'A',1,'30号买的'

select * from [TB]

SELECT item,price,comments FROM (
SELECT ROW_NUMBER()OVER (PARTITION BY item ORDER BY item,price DESC) AS NO ,*
FROM dbo.TB)T
WHERE no =1

/*
item	price	comments
A	8	3号买的
B	9	20号买的*/

[-晴天]

create table tb(item varchar(10),price int,comments nvarchar(20))
insert into tb select 'A',8,'3号买的'
insert into tb select 'B',2,'10号买的'
insert into tb select 'A',3,'15号买的'
insert into tb select 'B',6,'17号买的'
insert into tb select 'B',9,'20号买的'
insert into tb select 'A',1,'30号买的'
go
select * from tb a where not exists(select 1 from tb where item=a.item and price>a.price)
/*
item       price       comments
---------- ----------- --------------------
A          8           3号买的
B          9           20号买的

(2 行受影响)

*/

[dawugui]

select t.* from tb t where not exists(select 1 from tb where item = t.item and price > t.price)

select t.* from tb t where price = (select max(price) from tb where item = t.item)

[临窗飞飞]

[Quote=引用 3 楼 orchidcat 的回复:]
SQL code

if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (item nvarchar(2),price int,comments nvarchar(10))
insert into [TB]
select 'A',8,'3号买的' union all
select 'B',2,'1……
[/Quote]

[b]
经测试，只有3楼的这位前辈输出结果是对的！这是我简化了的表，我做SAP数据库维护，要做查询报表，查询的数据是所列简化的表的很多很多倍[/b]