取每组最大值中的一条数据
CREATE table TEST1(FID int, ItemID varchar(1), QTY int)
insert into TEST1
select 1, 'a', 10 union all
select 2, 'a', 20 union all
select 3, 'a', 40 union all
select 4, 'a', 30 union all
select 5, 'a', 40 union all
select 1, 'b', 15 union all
select 2, 'b', 45 union all
select 3, 'b', 25 union all
select 4, 'b', 35 union all
select 5, 'b', 45
select * from TEST1 t
where QTY=(select max(QTY) from TEST1 where ItemID=t.ItemID)
and FID=(select min(FID) from TEST1 where ItemID=t.ItemID and QTY=t.QTY)
/*
FID ItemID QTY
----------- ------ -----------
2 b 45
3 a 40
*/
