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

最新推荐文章于 2023-01-15 11:59:38 发布

原创最新推荐文章于 2023-01-15 11:59:38 发布 · 2.7k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#产品 #join

SQL 专栏收录该内容

12 篇文章

订阅专栏

本文介绍了一种使用SQL查询来获取产品最新、最高、最低及平均价格的方法。通过内连接子查询，实现了对重复ERP编码的数据进行有效处理，最终得到每个产品的价格详情。

表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