如何在MySQL中合并行的初始状态与最新状态？

完全可以用MySQL直接实现这个需求，而且效率会比PHP脚本高很多——数据库层面处理数据能避免应用层的IO开销和循环遍历的资源消耗。下面基于常见的表结构假设，给出具体的实现方案：

表结构假设 #

先明确两张表的典型结构（如果你的表结构略有不同，对应调整字段名即可）：

• customer表（存储最新状态）：
  • customer_id INT PRIMARY KEY（客户唯一ID）
  • name VARCHAR(100)（客户姓名）
  • email VARCHAR(100)（客户邮箱）
  • phone VARCHAR(20)（客户电话）
  • last_updated DATETIME（最后更新时间）
• audit_log表（存储修改日志）：
  • log_id INT PRIMARY KEY AUTO_INCREMENT（日志ID）
  • customer_id INT（关联客户ID）
  • field VARCHAR(50)（被修改的字段名，如name/email）
  • old_value TEXT（修改前的字段值）
  • new_value TEXT（修改后的字段值）
  • log_time DATETIME（日志记录时间）

实现步骤 #

1. 获取客户最新状态 #

这一步直接查询customer表即可，逻辑简单：

SELECT
  customer_id,
  name,
  email,
  phone,
  '最新状态' AS status_type
FROM customer;

2. 还原客户初始状态 #

要还原初始状态，需要从audit_log中提取每个客户每个字段的最早修改前的值；如果客户从未被修改过（无对应日志），则初始状态等于最新状态。

用窗口函数ROW_NUMBER()筛选每个字段的最早日志，再通过CASE WHEN将行转列拼接成完整的初始状态记录：

WITH customer_initial_fields AS (
  SELECT
    customer_id,
    field,
    old_value AS initial_value,
    -- 按客户+字段分组，按日志时间升序取第一条（最早的修改记录）
    ROW_NUMBER() OVER (PARTITION BY customer_id, field ORDER BY log_time ASC) AS rn
  FROM audit_log
),
initial_status AS (
  -- 有修改记录的客户，提取各字段初始值
  SELECT
    customer_id,
    MAX(CASE WHEN field = 'name' THEN initial_value END) AS name,
    MAX(CASE WHEN field = 'email' THEN initial_value END) AS email,
    MAX(CASE WHEN field = 'phone' THEN initial_value END) AS phone
  FROM customer_initial_fields
  WHERE rn = 1
  GROUP BY customer_id
  -- 合并无修改记录的客户，初始状态等于最新状态
  UNION ALL
  SELECT
    c.customer_id,
    c.name,
    c.email,
    c.phone
  FROM customer c
  LEFT JOIN audit_log al ON c.customer_id = al.customer_id
  WHERE al.customer_id IS NULL
)
SELECT
  customer_id,
  name,
  email,
  phone,
  '初始状态' AS status_type
FROM initial_status;

3. 合并初始与最新状态 #

将上述两个结果用UNION ALL合并，得到最终的完整输出：

WITH customer_initial_fields AS (
  SELECT
    customer_id,
    field,
    old_value AS initial_value,
    ROW_NUMBER() OVER (PARTITION BY customer_id, field ORDER BY log_time ASC) AS rn
  FROM audit_log
),
initial_status AS (
  SELECT
    customer_id,
    MAX(CASE WHEN field = 'name' THEN initial_value END) AS name,
    MAX(CASE WHEN field = 'email' THEN initial_value END) AS email,
    MAX(CASE WHEN field = 'phone' THEN initial_value END) AS phone
  FROM customer_initial_fields
  WHERE rn = 1
  GROUP BY customer_id
  UNION ALL
  SELECT
    c.customer_id,
    c.name,
    c.email,
    c.phone
  FROM customer c
  LEFT JOIN audit_log al ON c.customer_id = al.customer_id
  WHERE al.customer_id IS NULL
)
-- 合并最新状态与初始状态
SELECT
  customer_id,
  name,
  email,
  phone,
  '最新状态' AS status_type
FROM customer
UNION ALL
SELECT
  customer_id,
  name,
  email,
  phone,
  '初始状态' AS status_type
FROM initial_status
ORDER BY customer_id, status_type;

性能优化建议 #

为了让上述SQL高效运行，建议给audit_log表添加复合索引：

CREATE INDEX idx_audit_customer_logtime ON audit_log(customer_id, log_time);

这个索引会大幅提升窗口函数中分组排序的效率，避免全表扫描。

特殊情况处理 #

• 如果你的audit_log记录了创建操作（比如有一条field='create'的日志，new_value是客户的初始完整数据），可以直接提取这条日志的new_value来还原初始状态，无需按字段拆分处理。
• 如果字段类型不是TEXT（比如phone是INT），需要将old_value转换为对应类型，例如CAST(old_value AS UNSIGNED)。

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