PostgreSQL中如何查询指定行所有值为NOT NULL的列及对应值

注意前提 #

SQL语法要求执行前必须确定返回的列结构，无法做到动态根据数据调整返回列名，你可以根据使用场景选以下两种实现方案。

方案1：通用全数据库兼容（返回拼接好的非空键值对） #

这个方案返回单行结果，值里包含所有非空列名和对应值，所有数据库都能运行，修改表名和查询的id即可直接用：

SELECT 
  CONCAT_WS(',',
    CASE WHEN A IS NOT NULL THEN CONCAT('A:', A) ELSE NULL END,
    CASE WHEN B IS NOT NULL THEN CONCAT('B:', B) ELSE NULL END,
    CASE WHEN C IS NOT NULL THEN CONCAT('C:', C) ELSE NULL END,
    CASE WHEN D IS NOT NULL THEN CONCAT('D:', D) ELSE NULL END
  ) AS NOT_NULL_Columns
FROM 替换为你的表名
WHERE id = 1;

示例返回结果：A:测试值,C:123,D:2024-01-01
如果用PostgreSQL/MySQL 8.0以上版本，可以直接返回JSON结构更方便后续处理：

-- PostgreSQL版本
SELECT jsonb_strip_nulls(jsonb_build_object(
  'A', A, 'B', B, 'C', C, 'D', D
)) AS NOT_NULL_Columns
FROM 替换为你的表名
WHERE id = 1;

方案2：动态SQL实现（返回列仅包含非空字段） #

如果你要求返回结果的列名就是实际非空的字段名，可以用动态SQL实现，以下是MySQL示例：

-- 先指定要查询的id
SET @query_id = 1;
SET @dynamic_sql = NULL;

-- 拼接动态查询语句
SELECT CONCAT(
  'SELECT ', GROUP_CONCAT(COLUMN_NAME), ' FROM 替换为你的表名 WHERE id = ?'
) INTO @dynamic_sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '替换为你的表名'
AND COLUMN_NAME IN ('A','B','C','D')
AND (EXECUTE IMMEDIATE CONCAT('SELECT ', COLUMN_NAME, ' IS NOT NULL FROM 替换为你的表名 WHERE id = ', @query_id)) = 1;

-- 执行动态语句
PREPARE stmt FROM @dynamic_sql;
EXECUTE stmt USING @query_id;
DEALLOCATE PREPARE stmt;

执行后返回的结果列就是指定id行所有非空的字段，和你想要的SELECT NOT_NULL_Columns FROM table WHERE id=1效果一致。

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