SQL多列JOIN查询:获取满足A.DATE>=B.DATE的最新PRICE_LIST值
SQL关联取最新匹配价格的修正方案
你当前的SQL写法会触发一对多关联:表A每条记录会匹配到表B中所有符合A.DATE >= B.DATE的价格记录,因此无法直接得到最新的那条价格数据,可按以下两种方案修正:
方案1:窗口函数方案(适配MySQL8.0+、PostgreSQL、Hive、SparkSQL等支持窗口函数的数据库)
SELECT T.CUSTOMER, T.MATERIAL, T.DATE, T.PRICE_LIST FROM ( SELECT A.*, B.PRICE_LIST, -- 对同一条A记录匹配到的所有B记录,按B的日期倒序排序,最新的排第一位 ROW_NUMBER() OVER(PARTITION BY A.CUSTOMER, A.MATERIAL, A.DATE ORDER BY B.DATE DESC) AS RN FROM A LEFT JOIN B ON A.MATERIAL = B.MATERIAL AND A.CUSTOMER = B.CUSTOMER AND A.DATE >= B.DATE ) T WHERE T.RN = 1
方案2:子查询方案(适配MySQL5.x等不支持窗口函数的低版本数据库)
SELECT A.CUSTOMER, A.MATERIAL, A.DATE, B.PRICE_LIST FROM A LEFT JOIN ( -- 先匹配得到每条A记录对应的最新B记录日期 SELECT A.CUSTOMER, A.MATERIAL, A.DATE, MAX(B.DATE) AS MAX_B_DATE FROM A LEFT JOIN B ON A.MATERIAL = B.MATERIAL AND A.CUSTOMER = B.CUSTOMER AND A.DATE >= B.DATE GROUP BY A.CUSTOMER, A.MATERIAL, A.DATE ) T ON A.CUSTOMER = T.CUSTOMER AND A.MATERIAL = T.MATERIAL AND A.DATE = T.DATE LEFT JOIN B ON T.CUSTOMER = B.CUSTOMER AND T.MATERIAL = B.MATERIAL AND T.MAX_B_DATE = B.DATE
注:你给出的期望输出中后几行DATE字段统一写为2021-02-01属于手误,执行上述SQL后得到的正确结果如下:
CUSTOMER MATERIAL DATE PRICE_LIST X Y 2021-01-01 10 X Y 2021-02-01 11 X Y 2021-03-01 11 X Y 2021-04-01 11 X Y 2021-05-01 15 X Y 2021-06-01 15
内容的提问来源于stack exchange,提问作者Pedro Cintra
相关产品推荐
相关产品推荐

