MYSQL/ORACLE 一对多关联查询，取多表里最新的那一条数据

完整sql如下，t_qms_imp_car_info表中VIN_NO是唯一的，但是 t_qms_imp_iot_eol 表中VIN有重复，现在想两张表关联查询获取到t_qms_imp_iot_eol 中最新的那一条数据。

select car.VIN_NO as vin,      car.MODEL as scm,-- 实例化车型号      car.PRODUCT_DATE as productDate,-- 制造日期      car.ENGINE_NO as engineNo,      (CASE car.ENGINE_NO      WHEN '' THEN      '纯电动'      ELSE      '插电混动'      end) as carType,      (case xxjc.VIN when null then '未生成' when '' then '未生成' else '已生成' end) as createStatus,      (case xxjc.UPLOADE_STATUS when null then '未上传' when '0' then '未上传' when '1' then '已上传' end) as      uploadStatus,      xxjc.CREATED_DATE as zxCreateDate,      xxjc.UPLOADE_DATE as uploadDate,      xxjc.XXGKBM as xxgkbm,      t.bgbh,      t.zzpd,      t.obdResult,      t.pfjcResult,      t.wgjypd      from t_qms_imp_car_info car      left join t_qms_db_zs_xcxxjc xxjc on xxjc.VIN = car.VIN_NO      left join (        select * from (            select eol.VIN,                  eol.TEST_NO as bgbh,                  eol.CREATED_DATE,                  row_number() over(partition by eol.vin order by eol.CREATED_DATE desc) rn,                  (case eol.RESULT when '0' then '不通过' when '1' then '通过' end) as zzpd,                  (case eol.OPASS when '0' then '不通过' when '1' then '通过' end) as obdResult,                  (case eol.EPASS when '0' then '不通过' when '1' then '通过' end) as pfjcResult,                  (case eol.APASS when '0' then '不通过' when '1' then '通过' end) as wgjypd              from t_qms_imp_iot_eol eol        ) tab where tab.rn = 1      ) t on t.VIN = car.VIN_NO      where car.VIN_NO is not null

上段代码含义如下：

row_number() over(partition by eol.vin order by eol.CREATED_DATE desc) rn,

partition by eol.vin：是根据VIN分组；
order by eol.CREATED_DATE desc：分组后的数据再按创建时间排序；
row_number() over ：是对每一组的数据进行排序并给一个编号，从1开始。

单独运行分组排序那一段sql结果如下：

当想获取 t_qms_imp_iot_eol 表中的最新那条数据只需 where 条件查询 rn = 1 的即可。

来源地址：https://blog.csdn.net/zh555888/article/details/129163290