求查询语句,高手帮忙.
表1
PID     GetQty       CP
1         1                   A
1         3                   B  
2         3                   r  
2         6                   Z
3         9                   i
3         2                   W

仅查询各   PID   对应   GetQty   数量最大的记录(请勿用游标),如下.谢谢!

PID     GetQty       CP
1         3                   B  
2         6                   Z
3         9                   i


------解决方案--------------------
select a.* from 表1 a inner join
(select pid,max(getQty) as getQty from 表1 group by pid) b
on a.pid=b.pid and a.getqty=b.getqty
------解决方案--------------------
select a.*
from 表1 as a
inner join (select pid,max(getQty) as getQty from 表1 group by pid) as b
on a.pid=b.pid and a.getqty=b.getqty


--or


select a.*
from 表1 as a
where not exists (select * from 表1 where pid=a.pid and getqty> a.getqty)

------解决方案--------------------
----方法1:
select * from 表 as a where not exists(select 1 from 表 where PID=a.PID and GetQty> a.GetQty )
----方法2:
select * from 表 as a where GetQty = (select max(GetQty) from 表 where PID = a.PID )
order by PID
----方法3:
select a.* from 表 as a inner join (select PID,max(GetQty) as GetQty from 表 group by PID) as b
on b.PID = a.PID and a.GetQty = b.GetQty order by a.PID
------解决方案--------------------
----方法4:

select a.* from 表 as a left join 表 as b
on b.PID = a.PID and a.GetQty < b.GetQty
where b.PID is null

------解决方案--------------------
SELECT * FROM TEST AS A
WHERE A.Qty = ( Select max(B.Qty) from TEST AS B WHERE A.PID = B.PID)
ORDER BY A.PID