SQL查询：如何基于指定学生列表按学生与班级计算平均分（含无成绩班级）

现有数据表TABLE1：

TABLE1
    STUDENT CLASS   SCORE
    PAT     A       14
    PAT     A       10
    PAT     B       13
    PAT     B       20
    PAT     C       11
    MARK    A       18
    MARK    A       13
    MARK    A       17
    MARK    A       10
    DAVIS   C       12
    JER     A       15
    JER     B       12
    JER     C       20
    JER     C       17
    BARB    C       14
    BARB    C       19
    BARB    C       11
    TOM     A       18
    TOM     A       12
    TOM     B       16
    TOM     B       12

期望生成结果表：

STUDENT CLASS   AVERAGESCORE
PAT     A       12
PAT     B       16.5
PAT     C       11
DAVIS   A       NA
DAVIS   B       NA
DAVIS   C       12
MARK    A       14.5
MARK    B       NA
MARK    C       NA
JER     A       15
JER     B       12
JER     C       18.5

需求说明：

• 仅针对指定学生列表：PAT、MARK、DAVIS、JER
• 按学生+班级维度计算分数平均值（AVG(SCORE)）
• 无对应成绩的班级，平均值显示NA

尝试的SQL（未成功）：

LIST1 = PAT, MARK, DAVIS, JER
select STUDENT, CLASS, AVG(SCORE) OVER(PARTITION BY STUDENT, CLASS) AS AVERAGE_SCORE
from TABLE1
where STUDENT in LIST1

原SQL的问题是仅返回TABLE1中存在成绩的学生-班级组合，无法生成无成绩的组合。要实现需求，需要先生成指定学生与所有存在的班级的完整组合，再左连接原表计算平均值。

方案1：动态获取所有班级 #

WITH 
-- 定义目标学生列表
target_students AS (
    SELECT 'PAT' AS STUDENT UNION ALL
    SELECT 'MARK' UNION ALL
    SELECT 'DAVIS' UNION ALL
    SELECT 'JER'
),
-- 从原表中提取所有存在的班级（去重）
all_classes AS (
    SELECT DISTINCT CLASS FROM TABLE1
)
-- 生成全量学生-班级组合，左连接计算平均分
SELECT 
    ts.STUDENT,
    ac.CLASS,
    -- 无成绩时显示NA，否则输出平均值
    CASE 
        WHEN AVG(t.SCORE) IS NULL THEN 'NA' 
        ELSE CAST(AVG(t.SCORE) AS VARCHAR(10)) 
    END AS AVERAGESCORE
FROM target_students ts
CROSS JOIN all_classes ac
LEFT JOIN TABLE1 t 
    ON ts.STUDENT = t.STUDENT AND ac.CLASS = t.CLASS
GROUP BY ts.STUDENT, ac.CLASS
ORDER BY ts.STUDENT, ac.CLASS;

方案2：固定班级列表（已知仅A/B/C） #

如果确定班级只有A、B、C三类，可以简化代码：

WITH target_students AS (
    SELECT 'PAT' AS STUDENT UNION ALL
    SELECT 'MARK' UNION ALL
    SELECT 'DAVIS' UNION ALL
    SELECT 'JER'
)
SELECT 
    ts.STUDENT,
    c.CLASS,
    CASE 
        WHEN AVG(t.SCORE) IS NULL THEN 'NA' 
        ELSE CAST(AVG(t.SCORE) AS VARCHAR(10)) 
    END AS AVERAGESCORE
FROM target_students ts
CROSS JOIN (SELECT 'A' AS CLASS UNION ALL SELECT 'B' UNION ALL SELECT 'C') c
LEFT JOIN TABLE1 t 
    ON ts.STUDENT = t.STUDENT AND c.CLASS = t.CLASS
GROUP BY ts.STUDENT, c.CLASS
ORDER BY ts.STUDENT, c.CLASS;

核心逻辑说明 #

• CROSS JOIN生成指定学生与所有班级的全量组合，确保无成绩的班级也能出现在结果中
• LEFT JOIN关联原表，保留所有组合，无成绩的记录SCORE字段为NULL
• 用GROUP BY替代原SQL的窗口函数，避免重复输出同一学生-班级的多条记录
• CASE语句将NULL的平均值转换为NA，如果需要保留数值类型，可直接用AVG(t.SCORE)（数据库会显示NULL）

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