leetcode-1280-学生参加各科测试的次数

链接:1280. 学生们参加各科测试的次数 - 力扣（LeetCode）

前提条件:

学生表: Students

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| student_id    | int     |
| student_name  | varchar |
+---------------+---------+
在 SQL 中，主键为 student_id（学生ID）。
该表内的每一行都记录有学校一名学生的信息。

科目表: Subjects

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| subject_name | varchar |
+--------------+---------+
在 SQL 中，主键为 subject_name（科目名称）。
每一行记录学校的一门科目名称。

考试表: Examinations

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| subject_name | varchar |
+--------------+---------+
这个表可能包含重复数据（换句话说，在 SQL 中，这个表没有主键）。
学生表里的一个学生修读科目表里的每一门科目。
这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。

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

查询结构格式如下所示。

SQL语句

 CREATE TABLE Students (
    student_id INT,
    student_name VARCHAR(255)
);

CREATE TABLE Subjects (
    subject_name VARCHAR(255)
);

CREATE TABLE Examinations (
    student_id INT,
    subject_name VARCHAR(255)
);
INSERT INTO
        Students
VALUES
        (1, 'Alice'),
        (2, 'Bob'),
        (13, 'John'),
        (6, 'Alex');

INSERT INTO
        Subjects
VALUES
        ('Math'),
        ('Physics'),
        ('Programming');

INSERT INTO
        Examinations
VALUES
        (1, 'Math'),
        (1, 'Physics'),
        (1, 'Programming'),
        (2, 'Programming'),
        (1, 'Physics'),
        (1, 'Math'),
        (13, 'Math'),
        (13, 'Programming'),
        (13, 'Physics'),
        (2, 'Math'),
        (1, 'Math');

思路:

1. 查询内容：学生id（students）,学生姓名(students),课程名称(Subjects),参加次数，有的同学一次没有参加，有的多次参加
2. Students和Subjects用CROSS JOIN得到所有组合，以此表作为基础
3. Examinations按照student，subject_name得到student_id, subject_name, attends_name
4. 2 LEFT JOIN 3然后分组
5. 查询结果

优化:

实现:

1. SELECT
           stu.student_id,
           stu.student_name,
           sub.subject_name
   FROM
       students stu CROSS JOIN subjects sub;

  2.

SELECT
                    student_id, subject_name,COUNT(*) attend_exams
           FROM
                    Examinations
           GROUP BY
                    student_id, subject_name

3.

SELECT
        stu.student_id student_id,
        stu.student_name  student_name,
        sub.subject_name subject_name,
        ifnull(grouped.attend_exams , 0) attend_exams
FROM
       students stu CROSS JOIN  Subjects sub
                 LEFT JOIN (SELECT
                                     student_id, subject_name,COUNT(*) attend_exams
                            FROM
                                     Examinations
                            GROUP BY
                                     student_id, subject_name
                            )grouped ON stu.student_id = grouped.student_id AND
                                        sub.subject_name = grouped.subject_name
ORDER BY
    stu.student_id,sub.subject_name ;