如何求产品的最新,最高,最低,平均价格?

表rcyw_dj中有列:erpid(ERP编码),dj(单价),cjrq(创建日期);

说明:erpid可能会重复,每一erpid都有一dj,cjrq;

求最高,最低,平均价格比较简单:

select erpid,min(dj) as zdjg,max(dj) as zgjg,avg(dj) as pjjg from rcyw_dj where erpid like '911700%' group by erpid

求最新价格:

select rcyw_dj.erpid, rcyw_dj.dj as zxjg from rcyw_dj
inner join  (select erpid, max(cjrq) as cjrq from rcyw_dj where erpid like '911700%' group by erpid) a on rcyw_dj.erpid = a.erpid and rcyw_dj.cjrq = a.cjrq

将两条语句组合,最后得出产品的最新,最高,最低,平均价格:

select a.erpid, b.zxjg,a.zdjg,a.zgjg,a.pjjg from (
select erpid,min(dj) as zdjg,max(dj) as zgjg,avg(dj) as pjjg from rcyw_dj where erpid like '911700%' group by erpid) a inner join (
select rcyw_dj.erpid, rcyw_dj.dj as zxjg from rcyw_dj
inner join  (select erpid, max(djrq) as djrq from rcyw_dj where erpid like '911700%' group by erpid) a on rcyw_dj.erpid = a.erpid
 and rcyw_dj.djrq = a.djrq) b on a.erpid = b.erpid