如何筛选SQL表中非空值数量超过10的有效列？

以下针对主流SQL数据库给出具体实现方案：

MySQL/MariaDB 实现 #

通过系统表获取列名，生成动态SQL统计每列非空值数量，再筛选符合条件的列：

SET @table_name = '你的表名';
SET @schema_name = '你的数据库名'; -- 默认为'default'

-- 拼接统计每列非空数的SQL片段
SELECT GROUP_CONCAT(
    'COUNT(', column_name, ') AS ', column_name
) INTO @count_sql
FROM information_schema.columns
WHERE table_schema = @schema_name
  AND table_name = @table_name;

-- 生成完整查询SQL，将列转为行后筛选
SET @full_sql = CONCAT(
    'SELECT * FROM (SELECT ', @count_sql, ' FROM ', @schema_name, '.', @table_name, ') AS stats UNPIVOT (non_null_count FOR column_name IN (', 
    (SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_schema = @schema_name AND table_name = @table_name), 
    ')) AS unpivoted WHERE non_null_count > 10;'
);

-- 执行动态SQL
PREPARE stmt FROM @full_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

注：如果是MySQL 8.0以下版本，需用UNION ALL替代UNPIVOT来实现列转行逻辑。

PostgreSQL 实现 #

利用系统表遍历列并统计，或通过jsonb简化逻辑：

方式1：遍历列统计 #

DO $$
DECLARE
    table_name text := '你的表名';
    schema_name text := 'public'; -- 默认schema
    column_rec record;
BEGIN
    -- 创建临时表存储结果
    CREATE TEMP TABLE IF NOT EXISTS column_stats (
        column_name text,
        non_null_count integer
    );

    -- 逐个列统计非空值数量
    FOR column_rec IN 
        SELECT column_name FROM information_schema.columns 
        WHERE table_schema = schema_name AND table_name = table_name
    LOOP
        EXECUTE format(
            'INSERT INTO column_stats VALUES (%L, (SELECT COUNT(%I) FROM %I.%I))',
            column_rec.column_name, column_rec.column_name, schema_name, table_name
        );
    END LOOP;

    -- 查询符合条件的列
    SELECT string_agg(column_name, ', ') INTO result
    FROM column_stats WHERE non_null_count > 10;

    RAISE NOTICE '符合条件的列：%', result;
END $$;

方式2：利用jsonb简化 #

SELECT 
    key AS column_name, 
    (SELECT COUNT(*) FROM 你的表名 WHERE (你的表名::jsonb -> key) IS NOT NULL) AS non_null_count
FROM (SELECT jsonb_object_keys(to_jsonb(你的表名)) FROM 你的表名 LIMIT 1) AS cols(key)
WHERE non_null_count > 10;

SQL Server 实现 #

通过系统视图生成动态SQL，结合UNPIVOT筛选结果：

DECLARE @table_name NVARCHAR(128) = N'你的表名';
DECLARE @schema_name NVARCHAR(128) = N'dbo'; -- 默认schema
DECLARE @sql NVARCHAR(MAX);

-- 生成统计每列非空数的SQL
SET @sql = N'
SELECT column_name, non_null_count
FROM (
    SELECT ' + STUFF(
        (SELECT N', COUNT(' + QUOTENAME(c.name) + N') AS ' + QUOTENAME(c.name)
         FROM sys.columns c
         JOIN sys.tables t ON c.object_id = t.object_id
         JOIN sys.schemas s ON t.schema_id = s.schema_id
         WHERE s.name = @schema_name AND t.name = @table_name
         FOR XML PATH(''), TYPE
    ).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'') + N'
    FROM ' + QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name) + N'
) AS stats
UNPIVOT (
    non_null_count FOR column_name IN (' + STUFF(
        (SELECT N', ' + QUOTENAME(c.name)
         FROM sys.columns c
         JOIN sys.tables t ON c.object_id = t.object_id
         JOIN sys.schemas s ON t.schema_id = s.schema_id
         WHERE s.name = @schema_name AND t.name = @table_name
         FOR XML PATH(''), TYPE
    ).value(N'.[1]', N'NVARCHAR(MAX)'), 1, 2, N'') + N')
) AS unpivoted
WHERE non_null_count > 10;';

-- 执行动态SQL
EXEC sp_executesql @sql;

内容的提问来源于stack exchange，提问作者D.M.