SQL中动态获取学生表非空值及对应列名的实现方案

这个需求很常见，尤其是在处理这种宽表转窄表并过滤非空值的场景，我给你分几种常见数据库的实现方式，你可以按需选用：

核心思路 #

本质是把宽表的列转换成行（行转列的逆操作，也叫「unpivot」），然后过滤掉空值和指定学生之外的记录，最终得到该学生所有非空的科目及其分数，并且自动对应原列名。

一、固定科目列（静态实现） #

如果你的students表的科目列（Subject1、Subject2）不会新增，用这种方式最简单直接。

MySQL / MariaDB #

SELECT SubjectName, Score
FROM (
    -- 把每个科目列拆成单独的行
    SELECT Student, 'Subject1' AS SubjectName, Subject1 AS Score FROM students
    UNION ALL
    SELECT Student, 'Subject2' AS SubjectName, Subject2 AS Score FROM students
) AS unpivoted_data
-- 筛选指定学生 + 非空分数
WHERE Student = 'A'  -- 替换成你要查询的学生ID/姓名
  AND Score IS NOT NULL;

SQL Server #

用内置的UNPIVOT语法更简洁：

SELECT SubjectName, Score
FROM students
UNPIVOT (
    -- 把Subject1、Subject2的值转成Score列
    Score FOR SubjectName IN (Subject1, Subject2)
) AS unpivoted_data
WHERE Student = 'B'
  AND Score IS NOT NULL;

PostgreSQL #

用JSON函数快速实现列转行：

SELECT key AS SubjectName, value AS Score
FROM students,
     -- 把行转成JSON并移除Student字段，再拆分键值对
     json_each_text(row_to_json(students) - 'Student')
WHERE Student = 'C'
  AND value IS NOT NULL
  AND value <> '';  -- 如果有空白字符串需要过滤，加上这行

二、动态适配科目列（自动支持新增科目） #

如果以后可能新增Subject3、Subject4等列，不想每次修改代码，可以用动态SQL自动读取表结构生成查询。

MySQL / MariaDB（存储过程实现） #

DELIMITER //
CREATE PROCEDURE GetStudentNonEmptyScores(IN targetStudent VARCHAR(255))
BEGIN
    SET @dynamic_sql = NULL;
    -- 自动读取所有非Student的列，拼接查询语句
    SELECT GROUP_CONCAT(
        CONCAT(
            'SELECT ''', column_name, ''' AS SubjectName, ', column_name, ' AS Score FROM students WHERE Student = ''', targetStudent, ''' AND ', column_name, ' IS NOT NULL'
        )
        SEPARATOR ' UNION ALL '
    ) INTO @dynamic_sql
    FROM information_schema.columns
    WHERE table_schema = DATABASE()  -- 当前数据库
      AND table_name = 'students'
      AND column_name != 'Student';

    -- 执行动态SQL
    PREPARE stmt FROM @dynamic_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用示例：查询学生B的非空记录
CALL GetStudentNonEmptyScores('B');

SQL Server（动态SQL） #

DECLARE @targetStudent VARCHAR(255) = 'C';
DECLARE @subjectColumns NVARCHAR(MAX);
DECLARE @dynamicSql NVARCHAR(MAX);

-- 自动获取所有非Student的列名
SELECT @subjectColumns = STRING_AGG(QUOTENAME(column_name), ', ')
FROM information_schema.columns
WHERE table_schema = 'dbo'  -- 替换成你的表所属schema
  AND table_name = 'students'
  AND column_name != 'Student';

-- 拼接动态UNPIVOT查询
SET @dynamicSql = N'
SELECT SubjectName, Score
FROM students
UNPIVOT (
    Score FOR SubjectName IN (' + @subjectColumns + ')
) AS unpivoted_data
WHERE Student = ''' + @targetStudent + '''
  AND Score IS NOT NULL;
';

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

PostgreSQL（无需额外动态SQL） #

PostgreSQL的JSON方法天生支持动态列，不管新增多少科目列，下面的代码都能自动适配：

SELECT key AS SubjectName, value AS Score
FROM students,
     json_each_text(row_to_json(students) - 'Student')
WHERE Student = 'A'
  AND value IS NOT NULL
  AND value <> '';

效果验证 #

• 查询学生A：返回两行（Subject1 123、Subject2 1）
• 查询学生B：返回一行（Subject2 4）
• 查询学生C：返回一行（Subject1 122）

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