求查询语句,高手帮忙.
表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