前言
一对多产生笛卡尔积
需求:A表一条数据对B表多条数据中一条数据(且这个数据经过排序选择最大的一条)
如图:
A表为车辆
B表为价格
需求
获取一个车的最新价格!
371745 202203 1 125900
尝试过很多中转法,最后使用虚表加开窗函数解决
SQL
-- A 表
with a as(
select ROW_NUMBER() OVER (partition BY version_id ORDER BY mv.on_sale desc ) AS rid,MV.* from MA_VERSION MV WHERE mv.on_sale=1
),
-- B 表
b as(
select ROW_NUMBER() OVER (partition BY version_id ORDER BY ym_id desc,price_batch_id desc) AS rid ,spsy.* from SU_PRICE_STATE_YM spsy
)
select a.Version_Id,a.VERSION_NAME,b.ym_id,b.price_batch_id,b.MSRP from a
FULL JOIN b ON b.rid=a.rid AND a.version_id=b.version_id where a.SUB_MODEL_ID=66890007 order by b.MSRP desc