基于ID合并SQL表非空列、取各字段最新非空值的实现方法

实现方案 #

首先明确前提：你需要先确定表中用来判断记录新旧顺序的字段（比如创建时间create_time、自增流水IDlog_id等，值越新代表记录越晚生成），以下方案中统一用sort_col代指该字段，使用时替换为你的实际字段即可。

方案1：窗口函数（支持MySQL8.0+、PostgreSQL、Oracle、SQL Server等绝大多数主流新版数据库，推荐优先使用） #

利用FIRST_VALUE窗口函数按规则取每个字段的最新非空值：

SELECT DISTINCT
  id,
  FIRST_VALUE(col1) OVER (
    PARTITION BY id 
    ORDER BY CASE WHEN col1 IS NOT NULL THEN sort_col ELSE NULL END DESC NULLS LAST
  ) AS col1,
  FIRST_VALUE(col2) OVER (
    PARTITION BY id 
    ORDER BY CASE WHEN col2 IS NOT NULL THEN sort_col ELSE NULL END DESC NULLS LAST
  ) AS col2,
  FIRST_VALUE(col3) OVER (
    PARTITION BY id 
    ORDER BY CASE WHEN col3 IS NOT NULL THEN sort_col ELSE NULL END DESC NULLS LAST
  ) AS col3
  -- 剩余字段按照上面的规则类推即可
FROM your_table;

逻辑说明：按ID分组后，每个字段单独排序，有值的记录按sort_col倒序排在最前面，空值排在最后，取排在第一个的就是该ID下该字段的最新非空值。

方案2：分组聚合（适配MySQL5.x等不支持窗口函数的旧版数据库） #

利用GROUP_CONCAT和SUBSTRING_INDEX组合实现取值：

SELECT
  id,
  SUBSTRING_INDEX(GROUP_CONCAT(col1 ORDER BY sort_col DESC SEPARATOR '||'), '||', 1) AS col1,
  SUBSTRING_INDEX(GROUP_CONCAT(col2 ORDER BY sort_col DESC SEPARATOR '||'), '||', 1) AS col2,
  SUBSTRING_INDEX(GROUP_CONCAT(col3 ORDER BY sort_col DESC SEPARATOR '||'), '||', 1) AS col3
FROM your_table
GROUP BY id;

注意事项：该方案受group_concat_max_len参数限制，若字段值长度较大或同ID下记录数过多，需要提前调大该参数避免内容截断。

方案3：关联子查询（仅推荐小表场景使用） #

SELECT DISTINCT
  t1.id,
  (SELECT col1 FROM your_table t2 WHERE t2.id = t1.id AND col1 IS NOT NULL ORDER BY sort_col DESC LIMIT 1) AS col1,
  (SELECT col2 FROM your_table t2 WHERE t2.id = t1.id AND col2 IS NOT NULL ORDER BY sort_col DESC LIMIT 1) AS col2,
  (SELECT col3 FROM your_table t2 WHERE t2.id = t1.id AND col3 IS NOT NULL ORDER BY sort_col DESC LIMIT 1) AS col3
FROM your_table t1;

通用注意事项 #

• 如果你的表中空值是空字符串而非NULL，将所有判断条件中的IS NOT NULL替换为!= ''即可
• 若排序字段是时间类型，要注意时区、时间精度问题，避免排序逻辑不符合预期

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