如何用迭代法统计数据库表15列非空值并合并结果存入表

如果要避免手动编写15次重复的统计语句，动态SQL是最适合的迭代实现方式——通过查询系统元数据自动获取列名，循环生成每个列的统计逻辑，再拼接成完整的UNION ALL语句，最终将结果插入目标表。

以下是针对不同数据库的实现示例：

1. SQL Server 实现 #

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX), @targetTable NVARCHAR(128) = 'target_schema.target_table';

-- 从系统视图获取需要统计的列名（排除datasource列）
SELECT @sql = STRING_AGG(
  CONCAT(
    'SELECT ''', column_name, ''' AS column_name, COUNT(', QUOTENAME(column_name), ') AS non_null_count ',
    'FROM schema.tablename ',
    'WHERE ', QUOTENAME(column_name), ' IS NOT NULL AND datasource = ''xxx'''
  ),
  ' UNION ALL '
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'schema'
  AND TABLE_NAME = 'tablename'
  AND column_name NOT IN ('datasource');

-- 将统计结果插入目标表
SET @sql = CONCAT('INSERT INTO ', @targetTable, ' (column_name, non_null_count) ', @sql);

EXEC sp_executesql @sql;

逻辑说明 #

• 利用INFORMATION_SCHEMA.COLUMNS获取目标表的所有列名，排除不需要的datasource列
• 使用STRING_AGG迭代拼接每个列的统计语句，自动生成UNION ALL连接的完整SQL
• 执行动态SQL，将结果插入指定的目标表

2. MySQL 实现 #

SET @sql = NULL;
SET @targetTable = 'target_schema.target_table';

-- 生成迭代后的统计语句
SELECT GROUP_CONCAT(
  CONCAT(
    'SELECT ''', COLUMN_NAME, ''' AS column_name, COUNT(`', COLUMN_NAME, '`) AS non_null_count ',
    'FROM schema.tablename ',
    'WHERE `', COLUMN_NAME, '` IS NOT NULL AND datasource = ''xxx'''
  ) SEPARATOR ' UNION ALL '
) INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'schema'
  AND TABLE_NAME = 'tablename'
  AND COLUMN_NAME NOT IN ('datasource');

-- 插入目标表
SET @sql = CONCAT('INSERT INTO ', @targetTable, ' (column_name, non_null_count) ', @sql);

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

逻辑说明 #

• 用GROUP_CONCAT替代SQL Server的STRING_AGG完成语句拼接
• 通过预处理语句执行动态生成的SQL，符合MySQL的语法规范

3. 非动态SQL替代方案（UNPIVOT） #

如果数据库支持UNPIVOT（如SQL Server、Oracle），也可以用列转行的方式避免重复代码，虽然不属于迭代生成语句，但同样能简化写法：

INSERT INTO target_schema.target_table (column_name, non_null_count)
SELECT column_name, COUNT(value) AS non_null_count
FROM (
  SELECT 
    datasource,
    columnname1 AS columnname1_val,
    columnname2 AS columnname2_val,
    -- 依次列出剩余13个列的别名
    columnname15 AS columnname15_val
  FROM schema.tablename
  WHERE datasource = 'xxx'
) AS src
UNPIVOT (
  value FOR column_name IN (columnname1_val, columnname2_val, ..., columnname15_val)
) AS unpvt
GROUP BY column_name;

这种方式需要手动列出所有列，适合列名固定且不常变更的场景。

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