如何动态遍历列，按Group分组统计存在值的列？

针对你这种有数万条记录、数百列的场景，手动列所有字段肯定不现实，下面给你整理了几种主流数据库的动态实现方案，不用硬编码所有列名，能自动遍历统计每个Group下有值的列：

MySQL 实现方案 #

思路 #

通过动态SQL自动生成所有列的判断逻辑，再按Group分组聚合有值的列名，最后把空结果替换为null。

代码示例 #

SET @cols = (
  SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
      'MAX(CASE WHEN `', column_name, '` IS NOT NULL AND TRIM(`', column_name, '`) != '''' THEN ''', column_name, ''' ELSE '''' END) AS ', column_name
    )
  )
  FROM information_schema.columns
  WHERE table_schema = DATABASE()
    AND table_name = 'your_table_name'
    AND column_name NOT IN ('ID', 'Group')
);

SET @sql = CONCAT(
  'SELECT `Group`, ',
  'IFNULL(NULLIF(GROUP_CONCAT(DISTINCT col SEPARATOR \'; \'), ''''), ''null'') AS Cols ',
  'FROM (',
    'SELECT `Group`, ', @cols, ' FROM your_table_name GROUP BY `Group`',
  ') AS t ',
  'UNPIVOT (col FOR cols IN (', REPLACE(@cols, ' AS ', ','), ')) AS unpvt ',
  'GROUP BY `Group`'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

说明 #

1. 先从information_schema.columns获取目标表中除了ID、Group之外的所有列名
2. 动态生成每个列的判断逻辑：只要该列在组内有非空非空字符串的值，就保留列名
3. 通过UNPIVOT把列转成行，再用GROUP_CONCAT去重合并列名
4. 最后用IFNULL和NULLIF把空结果转为null

PostgreSQL 实现方案 #

思路 #

利用PostgreSQL的JSONB类型，把每行数据转为键值对，过滤掉空值后提取键名，再按Group聚合去重。

代码示例 #

WITH column_names AS (
  SELECT column_name
  FROM information_schema.columns
  WHERE table_schema = 'public'
    AND table_name = 'your_table_name'
    AND column_name NOT IN ('ID', "Group")
),
row_data AS (
  SELECT 
    "Group",
    jsonb_object_keys(jsonb_strip_nulls(to_jsonb(t) - 'ID' - 'Group')) AS col
  FROM your_table_name t
)
SELECT 
  "Group",
  COALESCE(string_agg(DISTINCT col, '; '), 'null') AS Cols
FROM row_data
GROUP BY "Group"
UNION ALL
SELECT 
  "Group",
  'null' AS Cols
FROM your_table_name
WHERE "Group" NOT IN (SELECT DISTINCT "Group" FROM row_data)
GROUP BY "Group";

说明 #

1. jsonb_strip_nulls会自动去掉行中的空值字段，jsonb_object_keys提取剩下的键名（即有值的列）
2. string_agg聚合去重后的列名，用COALESCE处理空结果为null
3. 最后用UNION ALL补全那些完全没有值列的Group

SQL Server 实现方案 #

思路 #

用动态SQL生成UNPIVOT的逻辑，把列转成行后分组聚合，处理空结果。

代码示例 #

DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(column_name), ', ')
FROM information_schema.columns
WHERE table_schema = 'dbo'
  AND table_name = 'your_table_name'
  AND column_name NOT IN ('ID', 'Group');

SET @sql = CONCAT(
  'SELECT 
    "Group",
    ISNULL(NULLIF(STRING_AGG(DISTINCT col, ''; ''), ''''), ''null'') AS Cols
  FROM (
    SELECT 
      "Group",
      col
    FROM your_table_name
    UNPIVOT (
      val FOR col IN (', @cols, ')
    ) AS unpvt
    WHERE val IS NOT NULL AND TRIM(val) != ''''
  ) AS t
  GROUP BY "Group"
  UNION ALL
  SELECT 
    "Group",
    ''null'' AS Cols
  FROM your_table_name
  WHERE "Group" NOT IN (SELECT DISTINCT "Group" FROM (
    SELECT "Group" FROM your_table_name
    UNPIVOT (val FOR col IN (', @cols, ')) AS unpvt
    WHERE val IS NOT NULL AND TRIM(val) != ''''
  ) AS t)
  GROUP BY "Group"'
);

EXEC sp_executesql @sql;

说明 #

1. 动态获取列名后，用UNPIVOT把列转为行，过滤掉空值
2. STRING_AGG聚合去重列名，ISNULL和NULLIF处理空结果
3. 用UNION ALL补充无值列的Group记录

性能优化建议 #

• 给Group字段建立非聚集索引，能大幅提升分组统计的速度
• 如果数据库支持，可以先对数据做分区（按Group分区），进一步优化大规模数据的处理效率
• 避免在高峰时段执行，或者用批量处理的方式拆分任务

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