SQL连接两表并用第二表覆盖重叠字段获取订单最新状态

完全可以实现，无需修改原有核心查询逻辑，也不需要使用SET语句，只需额外关联状态表的最新记录子集即可，具体方案如下：

实现思路 #

• 首先构造子查询提取每个订单的最新未删除状态：同一个Order#下，取Date最大的状态记录即可，若状态表Entry为自增字段，也可通过最大Entry判断最新记录
• 将原有查询的结果与该最新状态子集通过Order#关联，最终查询时优先取最新状态子集的字段覆盖原有重叠字段，即可得到预期结果

示例代码 #

SELECT 
  latest_st.Entry AS Entry,
  base_query.`Order#`,
  base_query.DatePlaced,
  base_query.Type,
  latest_st.Status,
  latest_st.`Date`,
  latest_st.Deleted
FROM (
  -- 此处保留你无法修改的原有查询，注意原别名or为SQL保留字，建议调整为o避免执行错误
  SELECT * FROM orders o
  LEFT JOIN status st ON o.entry = st.entry
  WHERE st.deleted = 0
) base_query
-- 额外关联每个订单的最新状态记录
INNER JOIN (
  SELECT s.*
  FROM status s
  INNER JOIN (
    SELECT `Order#`, MAX(`Date`) AS latest_status_date 
    FROM status 
    WHERE deleted = 0 
    GROUP BY `Order#`
  ) max_status ON s.`Order#` = max_status.`Order#` AND s.`Date` = max_status.latest_status_date
  WHERE s.deleted = 0
) latest_st ON base_query.`Order#` = latest_st.`Order#`

逻辑验证 #

针对你给出的示例数据：

1. 内层原有查询返回Entry为2001的订单基础数据
2. 最新状态子查询会筛选出Order#为5、Date为2021-05-05的Entry=2002的状态记录
3. 关联后最终返回的就是你需要的预期结果，原有订单基础信息保留，状态相关字段全部替换为最新值

内容的提问来源于stack exchange，提问作者chavez2021