基于6个含空值分数列动态计算学生加权平均分（最多取3个）

核心思路：行转列+排序筛选 #

把宽表格式的学生月度分数转为窄表，按月份优先级（scoremonth1最高）排序后取前3个非空分数，再聚合计算平均分，同时可直接追踪参与计算的月份，完全避免CASE WHEN枚举的繁琐。

1. 行转列处理数据 #

将每个学生的6个月度分数拆分为单条记录，同时标记对应的月份优先级（1代表最高优先级的scoremonth1，以此类推）。

适配SQL Server/PostgreSQL（支持UNPIVOT）：

SELECT 
    student_id,
    student_name,
    score,
    month_priority
FROM 
    student_scores
UNPIVOT (
    score FOR month_priority IN (
        scoremonth1 AS 1,
        scoremonth2 AS 2,
        scoremonth3 AS 3,
        scoremonth4 AS 4,
        scoremonth5 AS 5,
        scoremonth6 AS 6
    )
) AS unpivoted_scores
WHERE score IS NOT NULL

适配MySQL（无UNPIVOT，用UNION ALL模拟）：

SELECT student_id, student_name, scoremonth1 AS score, 1 AS month_priority FROM student_scores WHERE scoremonth1 IS NOT NULL
UNION ALL
SELECT student_id, student_name, scoremonth2 AS score, 2 AS month_priority FROM student_scores WHERE scoremonth2 IS NOT NULL
UNION ALL
SELECT student_id, student_name, scoremonth3 AS score, 3 AS month_priority FROM student_scores WHERE scoremonth3 IS NOT NULL
UNION ALL
SELECT student_id, student_name, scoremonth4 AS score, 4 AS month_priority FROM student_scores WHERE scoremonth4 IS NOT NULL
UNION ALL
SELECT student_id, student_name, scoremonth5 AS score, 5 AS month_priority FROM student_scores WHERE scoremonth5 IS NOT NULL
UNION ALL
SELECT student_id, student_name, scoremonth6 AS score, 6 AS month_priority FROM student_scores WHERE scoremonth6 IS NOT NULL

2. 计算平均分并追踪参与月份 #

用窗口函数ROW_NUMBER()按学生分组，按优先级升序排序后取前3条记录，再聚合计算平均分，同时用字符串拼接函数记录参与计算的月份：

WITH ranked_scores AS (
    SELECT 
        student_id,
        student_name,
        score,
        month_priority,
        ROW_NUMBER() OVER (PARTITION BY student_id ORDER BY month_priority ASC) AS rn
    FROM (
        -- 替换为上面对应的行转列SQL
        SELECT student_id, student_name, scoremonth1 AS score, 1 AS month_priority FROM student_scores WHERE scoremonth1 IS NOT NULL
        UNION ALL
        SELECT student_id, student_name, scoremonth2 AS score, 2 AS month_priority FROM student_scores WHERE scoremonth2 IS NOT NULL
        UNION ALL
        SELECT student_id, student_name, scoremonth3 AS score, 3 AS month_priority FROM student_scores WHERE scoremonth3 IS NOT NULL
        UNION ALL
        SELECT student_id, student_name, scoremonth4 AS score, 4 AS month_priority FROM student_scores WHERE scoremonth4 IS NOT NULL
        UNION ALL
        SELECT student_id, student_name, scoremonth5 AS score, 5 AS month_priority FROM student_scores WHERE scoremonth5 IS NOT NULL
        UNION ALL
        SELECT student_id, student_name, scoremonth6 AS score, 6 AS month_priority FROM student_scores WHERE scoremonth6 IS NOT NULL
    ) AS unpivoted
)
SELECT 
    student_id,
    student_name,
    AVG(score) AS average_score,
    GROUP_CONCAT(CONCAT('scoremonth', month_priority) ORDER BY month_priority ASC SEPARATOR ', ') AS used_months
FROM ranked_scores
WHERE rn <= 3
GROUP BY student_id, student_name;

3. 获取第二、第三个非空值的方法 #

如果不想用行转列，可通过GROUP_CONCAT结合字符串截取实现，直接提取按优先级排序后的第2、3个非空分数：

SELECT 
    student_id,
    student_name,
    -- 第一个非空值（最高优先级）
    COALESCE(scoremonth1, scoremonth2, scoremonth3, scoremonth4, scoremonth5, scoremonth6) AS first_non_null,
    -- 第二个非空值
    CASE WHEN COUNT(score) >=2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(score ORDER BY month_priority ASC SEPARATOR ','), ',', 2), ',', -1) END AS second_non_null,
    -- 第三个非空值
    CASE WHEN COUNT(score) >=3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(score ORDER BY month_priority ASC SEPARATOR ','), ',', 3), ',', -1) END AS third_non_null
FROM (
    SELECT 
        student_id,
        student_name,
        score,
        month_priority
    FROM student_scores
    UNION ALL
    -- 同上的UNION ALL行转列逻辑
) AS unpivoted
WHERE score IS NOT NULL
GROUP BY student_id, student_name;

注：若学生有效分数不足2或3个，对应字段会返回空值，符合需求。

方案优势 #

• 无需枚举所有CASE WHEN场景，新增月度分数时仅需修改行转列部分，扩展性强
• 原生支持追踪参与计算的月份，通过聚合拼接即可实现
• 基于SQL标准语法（除MySQL的UNPIVOT替代方案），适配绝大多数主流数据库

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