如何按状态匹配规则关联另一表数据更新目标表对应日期列

你这个需求属于典型的竖表转横表后关联更新，实现逻辑分为两步：先对第一张状态日志表按共同唯一ID做条件聚合，提取出不同状态对应的日期字段，再关联第二张目标表完成字段更新。

首先是核心的行转列查询，你可以先执行这段语句验证转换后的数据是否符合预期（示例中假设两张表的关联唯一键为id，你可以替换为自己实际的表名和字段名）：

SELECT 
  id,
  MAX(CASE WHEN status = 'CLAIMED' THEN DATETIME END) AS CLAIMED_DATETIME,
  MAX(CASE WHEN status = 'BOUGHT' THEN DATETIME END) AS BOUGHT_DATETIME,
  MAX(CASE WHEN status = 'RETURNED' THEN DATETIME END) AS RETURNED_DATETIME
FROM 第一张状态表名
GROUP BY id;

这里用MAX聚合函数是为了兼容同个ID同个状态存在多条日志的场景，会自动取最晚的日期；如果需要取最早的日期，替换为MIN即可。如果确认每个ID每个状态只会有一条记录，用MAX/MIN都不会影响结果。

接下来是关联更新的语句，不同数据库语法略有差异：

MySQL 写法 #

UPDATE 目标表名 oi
JOIN (
  SELECT 
    id,
    MAX(CASE WHEN status = 'CLAIMED' THEN DATETIME END) AS CLAIMED_DATETIME,
    MAX(CASE WHEN status = 'BOUGHT' THEN DATETIME END) AS BOUGHT_DATETIME,
    MAX(CASE WHEN status = 'RETURNED' THEN DATETIME END) AS RETURNED_DATETIME
  FROM 第一张状态表名
  GROUP BY id
) sl ON oi.id = sl.id
SET 
  oi.CLAIMED_DATETIME = sl.CLAIMED_DATETIME,
  oi.BOUGHT_DATETIME = sl.BOUGHT_DATETIME,
  oi.RETURNED_DATETIME = sl.RETURNED_DATETIME;

PostgreSQL 写法 #

UPDATE 目标表名 oi
SET 
  CLAIMED_DATETIME = sl.CLAIMED_DATETIME,
  BOUGHT_DATETIME = sl.BOUGHT_DATETIME,
  RETURNED_DATETIME = sl.RETURNED_DATETIME
FROM (
  SELECT 
    id,
    MAX(CASE WHEN status = 'CLAIMED' THEN DATETIME END) AS CLAIMED_DATETIME,
    MAX(CASE WHEN status = 'BOUGHT' THEN DATETIME END) AS BOUGHT_DATETIME,
    MAX(CASE WHEN status = 'RETURNED' THEN DATETIME END) AS RETURNED_DATETIME
  FROM 第一张状态表名
  GROUP BY id
) sl
WHERE oi.id = sl.id;

SQL Server 写法 #

UPDATE oi
SET 
  oi.CLAIMED_DATETIME = sl.CLAIMED_DATETIME,
  oi.BOUGHT_DATETIME = sl.BOUGHT_DATETIME,
  oi.RETURNED_DATETIME = sl.RETURNED_DATETIME
FROM 目标表名 oi
INNER JOIN (
  SELECT 
    id,
    MAX(CASE WHEN status = 'CLAIMED' THEN DATETIME END) AS CLAIMED_DATETIME,
    MAX(CASE WHEN status = 'BOUGHT' THEN DATETIME END) AS BOUGHT_DATETIME,
    MAX(CASE WHEN status = 'RETURNED' THEN DATETIME END) AS RETURNED_DATETIME
  FROM 第一张状态表名
  GROUP BY id
) sl ON oi.id = sl.id;

执行更新前建议先在测试环境验证，或者先通过SELECT语句关联确认匹配的记录和字段值无误后再执行更新操作。

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