如何编写SQL查询，找出表中任意行存在非空值的键列？

嘿，这个需求我之前在项目里碰到过，刚好可以分享几种实用的方法，具体选哪种取决于你的数据库系统和表的列数多少~

先明确需求 #

你要找的是表中的键列里，至少存在一行包含非空值的列（如果是我理解错了，比如实际是要找非键列，只需要调整下面示例中筛选列的逻辑即可）。下面分两种场景给出方案：

场景1：表的列数不多，手动写查询就行 #

如果你的表键列数量很少，直接用EXISTS逐个判断是最直观的方式。假设你的表叫your_table，键列是key_col1、key_col2、key_col3，可以这么写：

SELECT 'key_col1' AS non_null_key_columns
WHERE EXISTS (SELECT 1 FROM your_table WHERE key_col1 IS NOT NULL)

UNION ALL

SELECT 'key_col2'
WHERE EXISTS (SELECT 1 FROM your_table WHERE key_col2 IS NOT NULL)

UNION ALL

SELECT 'key_col3'
WHERE EXISTS (SELECT 1 FROM your_table WHERE key_col3 IS NOT NULL);

这个查询会返回所有至少有一行非空值的键列名称，逻辑简单易懂，也不需要依赖系统表。

场景2：表的列数很多，用系统表自动生成查询 #

如果键列数量多，手动写太麻烦，可以利用数据库的系统信息表动态生成查询，自动化完成检查。下面针对主流数据库给出示例：

MySQL/MariaDB #

MySQL可以通过information_schema.columns和key_column_usage筛选出键列，然后动态拼接执行SQL：

-- 替换成你的数据库和表名
SET @db_name = 'your_database';
SET @table_name = 'your_table';

-- 动态生成检查SQL
SELECT GROUP_CONCAT(
  CONCAT(
    'SELECT ''', column_name, ''' AS non_null_key_columns WHERE EXISTS (SELECT 1 FROM ', @table_name, ' WHERE ', column_name, ' IS NOT NULL)'
  ) SEPARATOR ' UNION ALL '
) INTO @sql
FROM information_schema.columns
WHERE table_schema = @db_name
  AND table_name = @table_name
  AND column_name IN (
    -- 筛选出键列（主键/唯一键）
    SELECT column_name 
    FROM information_schema.key_column_usage 
    WHERE table_schema = @db_name
      AND table_name = @table_name
  );

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

PostgreSQL #

PostgreSQL用pg_constraint和information_schema.columns来识别键列，通过PL/pgSQL循环检查：

DO $$
DECLARE
  rec record;
  result text[] := '{}'::text[];
BEGIN
  FOR rec IN 
    SELECT column_name
    FROM information_schema.columns
    WHERE table_schema = 'public' -- 替换成你的schema
      AND table_name = 'your_table' -- 替换成你的表名
      AND column_name IN (
        -- 筛选主键和唯一键列
        SELECT a.attname
        FROM pg_constraint c
        JOIN pg_class t ON c.conrelid = t.oid
        JOIN pg_namespace ns ON t.relnamespace = ns.oid
        JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(c.conkey)
        WHERE ns.nspname = 'public'
          AND t.relname = 'your_table'
          AND c.contype IN ('p', 'u') -- 'p'=主键，'u'=唯一键
      )
  LOOP
    -- 检查当前列是否有非空值
    EXECUTE format('SELECT 1 FROM your_table WHERE %I IS NOT NULL LIMIT 1', rec.column_name);
    IF FOUND THEN
      result := array_append(result, rec.column_name);
    END IF;
  END LOOP;
  -- 输出结果
  RAISE NOTICE '存在非空值的键列: %', result;
END $$;

SQL Server #

SQL Server通过sys.key_constraints识别键列，动态拼接执行查询：

DECLARE @sql NVARCHAR(MAX) = '';
DECLARE @table_name NVARCHAR(100) = 'your_table'; -- 替换成你的表名
DECLARE @schema_name NVARCHAR(100) = 'dbo'; -- 替换成你的schema

-- 动态生成检查SQL
SELECT @sql += 
  'SELECT ''' + COLUMN_NAME + ''' AS non_null_key_columns WHERE EXISTS (SELECT 1 FROM ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name) + ' WHERE ' + QUOTENAME(COLUMN_NAME) + ' IS NOT NULL) UNION ALL '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = @table_name
  AND COLUMN_NAME IN (
    -- 筛选主键和唯一键列
    SELECT c.name
    FROM sys.key_constraints kc
    JOIN sys.columns c ON kc.parent_object_id = c.object_id AND c.column_id IN (SELECT column_id FROM sys.index_columns WHERE object_id = kc.parent_object_id AND index_id = kc.unique_index_id)
    WHERE kc.type IN ('PK', 'UQ') -- 'PK'=主键，'UQ'=唯一键
      AND OBJECT_NAME(kc.parent_object_id) = @table_name
  );

-- 移除最后多余的UNION ALL
SET @sql = LEFT(@sql, LEN(@sql) - 10);

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

小提示 #

• 如果你的“键列”是业务层面的定义（不是数据库的主键/唯一键），只需要把上面示例中筛选键列的部分换成你手动指定的键列列表即可
• EXISTS查询效率很高，因为只要找到一行非空值就会停止扫描，不会遍历全表

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