MySQL 按TMDate合并同一客户多行数据为单行取最新联系方式时出现重复记录的问题

当前使用phoneemail表存储客户联系方式数据，表字段包含ID、NAME、PHONENO、EMAIL、TMDate。
业务规则：

• 同一客户的ID、NAME固定不变
• 当客户的PHONENO或EMAIL发生更新时，系统会新增一行记录，TMDate对应该条记录的采集时间

需求目标：
按客户维度合并数据，同一客户仅输出一行结果，分别取该客户最新的非空PHONENO、最新的非空EMAIL。

之前的实现思路：先通过子查询基于最大TMDate筛选出各客户的最新手机号，再用相同逻辑筛选各客户的最新邮箱，最后将两个结果集做内连接得到最终结果。但实际执行后返回重复记录，无法得到预期的单行输出。

原有实现没有对两个子查询的结果做按客户的单行收敛，一旦某个客户的手机号、邮箱对应的筛选结果不是1行，内连接时就会产生笛卡尔积，最终出现重复行：

• 筛选最新手机号时，若没有过滤非空值，或同一客户下存在多条同TMDate的手机号记录，子查询返回的行数会大于1
• 筛选最新邮箱时同理，多返回的行数和手机号子查询的行数做内连接匹配时，就会拼接出无效重复记录。比如某客户的手机号子查询返回2行、邮箱子查询返回3行，内连接后会生成6条重复记录，完全不符合预期。

适配MySQL 8.0及以上版本（支持窗口函数，性能最优） #

直接用窗口函数按客户分区，对非空的联系方式按采集时间倒序排序，取每个客户的第一条有效记录，从根源上避免笛卡尔积：

SELECT
  ID,
  NAME,
  latest_phoneno,
  latest_email
FROM (
  SELECT
    ID,
    NAME,
    -- 取当前客户最新的非空手机号
    FIRST_VALUE(PHONENO) OVER (
      PARTITION BY ID, NAME
      ORDER BY CASE WHEN PHONENO IS NOT NULL AND PHONENO != '' THEN TMDate ELSE '1970-01-01' END DESC
    ) AS latest_phoneno,
    -- 取当前客户最新的非空邮箱
    FIRST_VALUE(EMAIL) OVER (
      PARTITION BY ID, NAME
      ORDER BY CASE WHEN EMAIL IS NOT NULL AND EMAIL != '' THEN TMDate ELSE '1970-01-01' END DESC
    ) AS latest_email,
    -- 按客户排序后生成行号，用于后续过滤单行
    ROW_NUMBER() OVER (PARTITION BY ID, NAME ORDER BY TMDate DESC) AS rn
  FROM phoneemail
) t
WHERE rn = 1;

适配MySQL 5.x版本（无窗口函数支持） #

先分别对手机号、邮箱的最新非空值做单行收敛，再基于客户主键做左连接拼接，全程避免多对多关联：

-- 提取所有存在记录的客户基础信息
SELECT
  cb.ID,
  cb.NAME,
  lp.PHONENO AS latest_phoneno,
  le.EMAIL AS latest_email
FROM (
  SELECT DISTINCT ID, NAME FROM phoneemail
) cb
-- 关联每个客户的最新非空手机号（提前做单行收敛）
LEFT JOIN (
  SELECT pe.ID, pe.NAME, pe.PHONENO
  FROM phoneemail pe
  INNER JOIN (
    SELECT ID, MAX(TMDate) AS max_time
    FROM phoneemail
    WHERE PHONENO IS NOT NULL AND PHONENO != ''
    GROUP BY ID
  ) t ON pe.ID = t.ID AND pe.TMDate = t.max_time
  -- 同时间存在多条手机号的场景下，仅取一条避免行数膨胀
  GROUP BY pe.ID, pe.NAME
) lp ON cb.ID = lp.ID
-- 关联每个客户的最新非空邮箱（提前做单行收敛）
LEFT JOIN (
  SELECT pe.ID, pe.NAME, pe.EMAIL
  FROM phoneemail pe
  INNER JOIN (
    SELECT ID, MAX(TMDate) AS max_time
    FROM phoneemail
    WHERE EMAIL IS NOT NULL AND EMAIL != ''
    GROUP BY ID
  ) t ON pe.ID = t.ID AND pe.TMDate = t.max_time
  -- 同时间存在多条邮箱的场景下，仅取一条避免行数膨胀
  GROUP BY pe.ID, pe.NAME
) le ON cb.ID = le.ID;

• 筛选最新联系方式时，必须先过滤PHONENO/EMAIL的非空值，避免空值被当成有效记录参与时间排序
• 所有关联用的子查询结果，必须在关联前就按客户维度做单行收敛，禁止直接返回多行和其他结果集做关联
• 优先使用左连接替代内连接，既可以避免客户缺失某类联系方式时被过滤，也能从逻辑上减少多对多关联的概率

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