如何用SQL将学生成绩表的多列分数转为多行记录？

现有存储学生测试成绩的表assessment_scores，每行包含学生ID（fk_assigned_uid）及16个测试分数列（score1至score16）。需要编写SQL将每个学生ID与对应每个测试分数转为单独行。当前用UNION ALL实现3个分数列的转换，但扩展到16列过于繁琐，询问是否有更简便的单查询实现方式，若没有是否只能取整行后循环构建数组。

当前使用的繁琐实现代码：

SELECT 
   scores_uid
 , score1
FROM 
 assessment_scores WHERE fk_assigned_uid = '1000002'

UNION ALL 

SELECT 
   scores_uid
 , score2
FROM 
 assessment_scores WHERE fk_assigned_uid = '1000002'

UNION ALL 

SELECT 
   scores_uid
 , score3
FROM 
 assessment_scores WHERE fk_assigned_uid = '1000002';

不同数据库的简化实现方法 #

1. 支持数组展开的数据库（MySQL 8.0+/PostgreSQL/SQLite 3.33+） #

这类数据库可以通过数组构造+展开的方式，一次性处理所有分数列，无需重复写UNION ALL：

MySQL 8.0+ 示例

SELECT 
  s.scores_uid,
  s.fk_assigned_uid,
  score_val
FROM 
  assessment_scores s,
  UNNEST([score1, score2, score3, score4, score5, score6, score7, score8,
          score9, score10, score11, score12, score13, score14, score15, score16]) AS score_val
WHERE 
  s.fk_assigned_uid = '1000002';

PostgreSQL 示例

SELECT 
  s.scores_uid,
  s.fk_assigned_uid,
  unnest(ARRAY[score1, score2, score3, score4, score5, score6, score7, score8,
               score9, score10, score11, score12, score13, score14, score15, score16]) AS score_val
FROM 
  assessment_scores s
WHERE 
  s.fk_assigned_uid = '1000002';

2. 支持UNPIVOT语法的数据库（SQL Server/Oracle） #

这类数据库提供了专门的列转行语法，代码更简洁易读：

SQL Server 示例

SELECT 
  scores_uid,
  fk_assigned_uid,
  score_val
FROM 
  assessment_scores
UNPIVOT (
  score_val FOR score_col IN (score1, score2, score3, score4, score5, score6, score7, score8,
                              score9, score10, score11, score12, score13, score14, score15, score16)
) AS unpvt
WHERE 
  fk_assigned_uid = '1000002';

Oracle 示例

SELECT 
  scores_uid,
  fk_assigned_uid,
  score_val
FROM 
  assessment_scores
UNPIVOT INCLUDE NULLS (
  score_val FOR score_col IN (score1, score2, score3, score4, score5, score6, score7, score8,
                              score9, score10, score11, score12, score13, score14, score15, score16)
)
WHERE 
  fk_assigned_uid = '1000002';

3. 通用兼容方案（无特定函数支持时） #

如果你的数据库不支持上述方法，可以用一次表查询结合CROSS JOIN生成行，避免多次扫描表：

SELECT 
  s.scores_uid,
  s.fk_assigned_uid,
  CASE n.num
    WHEN 1 THEN s.score1
    WHEN 2 THEN s.score2
    WHEN 3 THEN s.score3
    WHEN 4 THEN s.score4
    WHEN 5 THEN s.score5
    WHEN 6 THEN s.score6
    WHEN 7 THEN s.score7
    WHEN 8 THEN s.score8
    WHEN 9 THEN s.score9
    WHEN 10 THEN s.score10
    WHEN 11 THEN s.score11
    WHEN 12 THEN s.score12
    WHEN 13 THEN s.score13
    WHEN 14 THEN s.score14
    WHEN 15 THEN s.score15
    WHEN 16 THEN s.score16
  END AS score_val
FROM 
  assessment_scores s
CROSS JOIN (
  SELECT 1 AS num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
  UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
  UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16
) n
WHERE 
  s.fk_assigned_uid = '1000002'
  -- 可选：如果要过滤空分数，加上下面的判断
  AND CASE n.num
    WHEN 1 THEN s.score1
    WHEN 2 THEN s.score2
    WHEN 3 THEN s.score3
    WHEN 4 THEN s.score4
    WHEN 5 THEN s.score5
    WHEN 6 THEN s.score6
    WHEN 7 THEN s.score7
    WHEN 8 THEN s.score8
    WHEN 9 THEN s.score9
    WHEN 10 THEN s.score10
    WHEN 11 THEN s.score11
    WHEN 12 THEN s.score12
    WHEN 13 THEN s.score13
    WHEN 14 THEN s.score14
    WHEN 15 THEN s.score15
    WHEN 16 THEN s.score16
  END IS NOT NULL;

关于应用层循环构建的选项 #

如果上述SQL方案都无法使用，取整行后在应用层循环构建是可行的：

• 先查询目标学生的整行数据
• 遍历16个分数字段，将每个分数与学生ID组合成新行

这种方式适合数据量不大的场景，操作简单易实现。

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