如何对多列数据进行值聚合？SQL多列非空值聚合方法咨询

嘿，针对你描述的场景——表中每行仅有0、1或2个非空值，需要把这些分散在不同列的非空值聚合起来，我整理了几种实用的SQL写法，适配不同的需求和数据库类型：

1. 把所有非空值合并成单个字段（最常用） #

如果你的目标是把每行的非空值用分隔符（比如逗号）拼在一起，用CONCAT_WS函数是最省心的——它会自动忽略NULL值，只处理非空内容，几乎所有主流数据库都支持：

SELECT 
  -- 用逗号分隔所有非空值，你可以换成其他分隔符
  CONCAT_WS(',', column1, column2, column3, ..., column_n) AS aggregated_values,
  -- 如果需要保留原表的所有列，直接列出来就行
  column1, column2, column3, ..., column_n
FROM your_table;

要是你的数据库比较老，不支持CONCAT_WS（比如早期版本的MySQL），可以用嵌套的COALESCE先处理单个非空值，再扩展到两个的情况：

SELECT 
  CASE
    -- 处理两个非空值的情况
    WHEN (column1 IS NOT NULL AND column2 IS NOT NULL) THEN CONCAT(column1, ',', column2)
    WHEN (column1 IS NOT NULL AND column3 IS NOT NULL) THEN CONCAT(column1, ',', column3)
    -- 这里可以继续补充其他两列非空的组合，或者用更灵活的方式
    -- 处理单个或全空的情况
    ELSE COALESCE(column1, column2, column3, ..., column_n, '')
  END AS aggregated_values,
  column1, column2, column3, ..., column_n
FROM your_table;

2. 把非空值分到固定的新列里（比如value1、value2） #

如果需要把每行的第一个非空值放到value1，第二个放到value2，可以利用数组相关的函数来实现：

PostgreSQL版本 #

SELECT 
  -- 提取数组里的第一个非空值
  (ARRAY_REMOVE(ARRAY[column1, column2, column3, ..., column_n], NULL))[1] AS value1,
  -- 提取第二个非空值（没有的话会返回NULL）
  (ARRAY_REMOVE(ARRAY[column1, column2, column3, ..., column_n], NULL))[2] AS value2,
  -- 保留原表列
  column1, column2, column3, ..., column_n
FROM your_table;

MySQL版本 #

MySQL可以借助JSON_TABLE来模拟数组提取：

SELECT 
  j.value1,
  j.value2,
  t.column1, t.column2, t.column3, ..., t.column_n
FROM your_table t
LEFT JOIN JSON_TABLE(
  JSON_ARRAYAGG(jv.value),
  '$[*]' COLUMNS(
    value1 VARCHAR(255) PATH '$[0]',
    value2 VARCHAR(255) PATH '$[1]'
  )
) j ON TRUE
CROSS JOIN JSON_TABLE(
  JSON_ARRAY(t.column1, t.column2, t.column3, ..., t.column_n),
  '$[*]' COLUMNS(
    value VARCHAR(255) PATH '$' WHERE value IS NOT NULL
  )
) jv
GROUP BY t.id, j.value1, j.value2; -- 这里假设表有主键id，用来分组确保每行对应正确

3. 标记非空值所在的列（可选需求） #

如果只是需要明确每行的非空值在哪些列，可以加一个标识字段：

SELECT 
  column1, column2, column3, ..., column_n,
  -- 列出所有非空列的名称，用逗号分隔
  CONCAT_WS(',',
    CASE WHEN column1 IS NOT NULL THEN 'column1' END,
    CASE WHEN column2 IS NOT NULL THEN 'column2' END,
    CASE WHEN column3 IS NOT NULL THEN 'column3' END,
    ...,
    CASE WHEN column_n IS NOT NULL THEN 'column_n' END
  ) AS non_null_columns
FROM your_table;

小提醒 #

不同数据库的函数细节会有差异：比如SQL Server用STRING_AGG替代CONCAT_WS做聚合拼接，Oracle用LISTAGG；如果非空值是不同数据类型（比如数字+字符串），记得先用CAST或CONVERT统一转成字符串再拼接哦。

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