SQL中如何关联Criteria表ID与Students表列实现分数对比？

你的需求是把Criteria表的marks available和Students表的对应成绩关联展示，但当前的表结构有个核心问题：Students表是宽表设计，每个Criteria的成绩单独成列（比如Criteria1 Mark Achieved），没有和Criteria表的Criteria ID直接对应的关联键。所以要实现需求，得先把Students表的成绩数据转换成"每行对应一个学生+一个评分项"的结构，再和Criteria表做关联。

具体实现步骤 #

1. 先把Students表的成绩列转成规范化行结构 #

如果你的数据库支持UNPIVOT（比如SQL Server），可以用这个语法快速转换；如果是通用SQL，用UNION ALL也能实现：

通用SQL（UNION ALL方式）

-- 转换Students表，生成带CriteriaID的成绩行
SELECT 
    StudentID,
    Name,
    1 AS CriteriaID,
    [Criteria1 Mark Achieved] AS MarkAchieved
FROM Students
UNION ALL
SELECT 
    StudentID,
    Name,
    2 AS CriteriaID,
    [Criteria2 Mark Achieved] AS MarkAchieved
FROM Students
UNION ALL
SELECT 
    StudentID,
    Name,
    3 AS CriteriaID,
    [Criteria3 Mark Achieved] AS MarkAchieved
FROM Students

2. 关联Criteria表得到最终结果 #

把上面的转换结果作为子查询，和Criteria表通过CriteriaID关联，就能拿到每个评分项的满分和学生的得分：

SELECT
    s.StudentID,
    s.Name,
    c.CriteriaID,
    c.description AS 评分项描述,
    c.[marks available] AS 满分,
    s.MarkAchieved AS 学生得分
FROM (
    -- 这里插入上面的UNION ALL转换语句
    SELECT 
        StudentID,
        Name,
        1 AS CriteriaID,
        [Criteria1 Mark Achieved] AS MarkAchieved
    FROM Students
    UNION ALL
    SELECT 
        StudentID,
        Name,
        2 AS CriteriaID,
        [Criteria2 Mark Achieved] AS MarkAchieved
    FROM Students
    UNION ALL
    SELECT 
        StudentID,
        Name,
        3 AS CriteriaID,
        [Criteria3 Mark Achieved] AS MarkAchieved
    FROM Students
) s
INNER JOIN Criteria c 
    ON s.CriteriaID = c.CriteriaID

更优的表结构建议 #

如果你的系统还在初期阶段，强烈建议把Students表的成绩部分拆成一个单独的规范化表（比如叫StudentCriteriaMarks），结构如下：

• StudentID（关联Students表）
• CriteriaID（关联Criteria表）
• MarkAchieved

这样后续新增评分项时，不需要修改表结构，查询也更简单：

SELECT
    st.StudentID,
    st.Name,
    c.CriteriaID,
    c.description,
    c.[marks available],
    scm.MarkAchieved
FROM Students st
INNER JOIN StudentCriteriaMarks scm 
    ON st.StudentID = scm.StudentID
INNER JOIN Criteria c 
    ON scm.CriteriaID = c.CriteriaID

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