力扣1280.学生们参加各科测试的次数

victory77

于 2025-02-05 12:56:03 发布

阅读量211

点赞数 3

文章标签： leetcode 算法职场和发展数据库 sql

本文链接：https://blog.youkuaiyun.com/victory77/article/details/145453180

版权

查询出每个学生参加每一门科目测试的次数，结果按 student_id 和 subject_name 排序。

在这里插入图片描述

SELECT
    s.student_id,
    s.student_name,
    su.subject_name,
   COUNT(e.subject_name) AS attended_exams
FROM
    Students AS s
INNER JOIN
    Subjects AS su
LEFT JOIN
    Examinations AS e
ON
    e.student_id = s.student_id
AND
    e.subject_name = su.subject_name
GROUP BY
    s.student_id,
    su.subject_name
ORDER BY
    s.student_id,
    su.subject_name