SQL Server查询时如何自动排除所有值均为NULL的结果列

数据老陈

2026-10-3

这个需求无法通过静态SQL实现，必须使用动态SQL：先校验每个列是否存在非NULL值，再根据校验结果拼接查询字段执行查询。
以下是不同数据库的实现示例：

MySQL

-- 拼接有非NULL值的列名
SET @query_columns = '';
SELECT GROUP_CONCAT(c.COLUMN_NAME) INTO @query_columns
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_SCHEMA = DATABASE() 
  AND c.TABLE_NAME = 'Employee'
  AND EXISTS (
    SELECT 1 FROM Employee WHERE 
      CASE c.COLUMN_NAME
        WHEN 'FirstName' THEN FirstName IS NOT NULL
        WHEN 'LastName' THEN LastName IS NOT NULL
        WHEN 'Address' THEN Address IS NOT NULL
        WHEN 'Position' THEN Position IS NOT NULL
      END
  );

-- 执行动态查询
SET @final_sql = CONCAT('SELECT ', @query_columns, ' FROM Employee');
PREPARE stmt FROM @final_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL

DO $$
DECLARE
    valid_columns text;
    final_sql text;
BEGIN
    SELECT string_agg(quote_ident(col), ', ') INTO valid_columns
    FROM (
        SELECT unnest(ARRAY['FirstName','LastName','Address','Position']) AS col
    ) t
    WHERE EXISTS (
        EXECUTE format('SELECT 1 FROM Employee WHERE %I IS NOT NULL', t.col)
    );

    final_sql := format('SELECT %s FROM Employee', valid_columns);
    EXECUTE final_sql;
END $$;

SQL Server

DECLARE @queryColumns NVARCHAR(MAX), @finalSQL NVARCHAR(MAX)

SELECT @queryColumns = COALESCE(@queryColumns + ', ', '') + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.TABLE_NAME = 'Employee'
AND EXISTS (
    EXEC('SELECT 1 FROM Employee WHERE ' + c.COLUMN_NAME + ' IS NOT NULL')
)

SET @finalSQL = 'SELECT ' + @queryColumns + ' FROM Employee'
EXEC sp_executesql @finalSQL