如何编写无需硬编码列名的SQL查询，统计表各列非空值数量？

我想写一条SQL查询，返回表中每一列及其非空值的计数，结果格式如下：

| C1 | C2 | C*
Count() 

针对单个列，我使用的语句是：

SELECT COUNT(NULLIF(E.Id,''))
  FROM Employee as E

我搞不懂怎么在不硬编码每个列名的情况下，对每一列执行NULLIF操作。

解决方案：用动态SQL实现 #

要避免硬编码列名，必须靠动态SQL生成查询语句——先从系统元数据中获取目标表的所有列名，再拼接出包含COUNT(NULLIF(列名, ''))的完整查询。以下是主流数据库的具体实现：

MySQL/MariaDB #

SET @table_name = 'Employee';
SET @sql = NULL;

SELECT GROUP_CONCAT(
  CONCAT('COUNT(NULLIF(`', column_name, '`, '''')) AS `', column_name, '`'
) INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = DATABASE()
  AND table_name = @table_name;

SET @sql = CONCAT('SELECT ', @sql, ' FROM ', @table_name);

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

SQL Server #

DECLARE @table_name NVARCHAR(128) = 'Employee';
DECLARE @sql NVARCHAR(MAX);

SELECT @sql = STRING_AGG(
  CONCAT('COUNT(NULLIF(', QUOTENAME(column_name), ', '''')) AS ', QUOTENAME(column_name)
, ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SCHEMA_NAME()
  AND TABLE_NAME = @table_name;

SET @sql = CONCAT('SELECT ', @sql, ' FROM ', @table_name);

EXEC sp_executesql @sql;

PostgreSQL #

DO $$
DECLARE
  table_name TEXT := 'Employee';
  sql TEXT;
BEGIN
  SELECT string_agg(
    format('COUNT(NULLIF(%I, '''')) AS %I', column_name, column_name)
  , ', ')
  INTO sql
  FROM information_schema.columns
  WHERE table_schema = current_schema()
    AND table_name = table_name;

  sql := 'SELECT ' || sql || ' FROM ' || table_name;
  EXECUTE sql;
END $$;

Oracle #

DECLARE
  table_name VARCHAR2(128) := 'EMPLOYEE';
  sql VARCHAR2(32767);
BEGIN
  SELECT LISTAGG(
    'COUNT(NULLIF(' || column_name || ', '')) AS ' || column_name
  , ', ') WITHIN GROUP (ORDER BY column_id)
  INTO sql
  FROM user_tab_columns
  WHERE table_name = UPPER(table_name);

  sql := 'SELECT ' || sql || ' FROM ' || table_name;
  EXECUTE IMMEDIATE sql;
END;
/

注意事项 #

• 上述代码会把空字符串''转为NULL后统计，最终结果同时排除了NULL和空字符串；如果只需要排除NULL，直接用COUNT(列名)即可。
• 执行该SQL的用户需要拥有读取系统元数据（如INFORMATION_SCHEMA.COLUMNS）的权限。

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