Oracle多表关联分组统计：按学生统计课程数、总费用及物品数

现有数据表 #

• 学生选课表（存储学生所选课程信息）：

class_id student_id
-------------------
 1      |    2
 2      |    2
 3      |    1

• 课程费用表（存储各课程的费用信息）：

class_id class_tuition_fee class_item_fee
-----------------------------------------
1       |     100         |     45
2       |     20          |     null
3       |     30          |     100

• 课程物品使用量表（存储各课程的物品使用总量）：

class_id item_quant
-------------------
1       |  2
2       |  null
3       |  4

统计需求 #

编写Oracle SQL语句，按学生维度统计以下信息：

• 所选课程数量
• 课程总费用（学费+物品费，物品费为null时按0计算）
• 课程物品使用总量（物品使用量为null时按0计算）

期望结果 #

student_id num_class total_fee num_item
-----------------------------------------
1         |  1      |  130    |  4
2         |  2      |  165    |  2
3         |  0      |  0      |  0

解决方案SQL #

WITH all_students AS (
    -- 构造所有需要统计的学生ID集合，若有单独学生表可直接替换为该表查询
    SELECT 1 AS student_id FROM DUAL
    UNION ALL
    SELECT 2 FROM DUAL
    UNION ALL
    SELECT 3 FROM DUAL
)
SELECT
    s.student_id,
    COUNT(sc.class_id) AS num_class,
    NVL(SUM(NVL(c.class_tuition_fee, 0) + NVL(c.class_item_fee, 0)), 0) AS total_fee,
    NVL(SUM(NVL(i.item_quant, 0)), 0) AS num_item
FROM all_students s
LEFT JOIN student_course sc ON s.student_id = sc.student_id
LEFT JOIN course_fee c ON sc.class_id = c.class_id
LEFT JOIN course_item_usage i ON sc.class_id = i.class_id
GROUP BY s.student_id
ORDER BY s.student_id;

逻辑说明 #

1. 学生集合构造：通过公共表表达式生成所有需统计的学生ID，若系统中存在独立学生表，直接替换为该表的student_id查询即可。
2. 左连接关联：用左连接保证未选课的学生也能被纳入统计范围。
3. NULL值处理：使用NVL将NULL值替换为0，避免求和或统计时出现NULL结果。
4. 统计规则：
   • COUNT(sc.class_id)自动忽略NULL值，精准统计学生选课数量；
   • 总费用先分别处理学费、物品费的NULL值再求和，最后确保未选课学生总费用为0；
   • 物品使用总量同理，处理NULL后求和，未选课学生结果为0。

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