mysql - 如何将一行的初始状态与 MySQL 中的最新状态结合起来？

Question

我需要从 MySQL 数据库中获取初始状态和最新状态。这是两个表:

customer

<表类="s-表"><头>id名字姓氏做电子邮件电话<正文>10史蒂夫鲍勃01-01-1970uvw@xyz.com012345678915詹姆斯债券01-01-1950007@bond.com0101010999

audit_log

<表类="s-表"><头>identity_id属性旧值新值<正文>110名字约翰史蒂夫210电子邮件abc@def.comuvw@xyz.com310电话0123456789

我期望的是这样的输出:

<表类="s-表"><头>id名字姓氏做电子邮件电话<正文>10史蒂夫鲍勃01-01-1970uvw@xyz.com012345678910_1约翰鲍勃01-01-1970abc@def.com15詹姆斯债券01-01-1950007@bond.com0101010999

我最初有一个 PHP 脚本遍历所有 customer 行，然后将它们与 audit_log 行匹配并从那里生成输出，但速度非常慢并且占用大量资源。

是否可以直接在 MySQL 中执行类似的操作，我该怎么做？

编辑

我在 customer 和输出表中添加了额外的行。输出表需要包含 customer 中的所有行，以及从 audit_log 构建的初始行的副本。

Answer 1

尝试以下操作:

SET @rn=0;
SET @cust=null;
SET @pr=null;

SELECT id, name, surname, dob, email, telephone
FROM customer 
  UNION ALL
(
  SELECT CONCAT(T.id, '_', D.rownum) id,
        COALESCE(MAX(CASE D.property WHEN 'name' THEN D.old_value END), MAX(T.name)) name,
        COALESCE(MAX(CASE D.property WHEN 'surname' THEN D.old_value END), MAX(T.surname)) surname,
        COALESCE(MAX(CASE D.property WHEN 'dob' THEN D.old_value END), MAX(T.dob)) dob,
        COALESCE(MAX(CASE D.property WHEN 'email' THEN D.old_value END), MAX(T.email)) email,
        COALESCE(MAX(CASE D.property WHEN 'telephone' THEN D.old_value END), MAX(T.telephone)) telephone
  FROM customer T
  JOIN 
  (
    SELECT id, entity_id, property, old_value, new_value, 
    IF(@cust <> entity_id OR @pr <> property, @rn:=1, @rn:=@rn+1) rownum,
    @cust:=entity_id, @pr:=property
    FROM audit_log 
    ORDER BY entity_id, property, id
  ) D
  ON T.ID = D.entity_id
  WHERE D.rownum=1
  GROUP BY T.id, D.rownum
)
ORDER BY id

查看 demo .

此查询模拟 ROW_NUMBER() OVER (PARTITION BY entity_id, property ORDER BY id)为 audit_log 表获取每个客户/属性(property)的初始值(其中 rownum = 1)。

COALESCE用于从 customers 表中获取属性的值，如果此属性未更改，即名称已更改但电子邮件未更改，则对于电子邮件获取最新值(在本例中为 latest=initial，即T.此查询中的电子邮件)。