mysql - SQL 表 - 根据其他 2 个表的条件计算 table1 的行数

Question

My problem is I want to count all the students under n department.
Note: Students has a course and courses has their department (there are many courses in 1 department). SAMPLE OUTPUT:

+---------+-------------------------------+
| student | department                    |
+---------+-------------------------------+
|       23| Computer Education Department |
|       67| Basic Education Department    |
|       39| Mathematics Department        |
|       40| Humanities Department         |
|       61| Engineering Department        |
|       79| Management Department         |
+---------+-------------------------------+

1. tbl_students

+---------+---------------+--------+| stud_id | name          | course |+---------+---------------+--------+|       1 | Jack Owen     |      1 ||       2 | Kirby Lopez   |      2 ||       3 | Justin Minus  |      1 ||       4 | Jerome Noveda |      1 |+---------+---------------+--------+

2。 tbl_类(class)

+-----------+------------+---------+| course_id | short_name | dept_id |+-----------+------------+---------+|         1 | BSIT       |       1 ||         2 | BSCS       |       1 ||         3 | BEED       |       2 ||         4 | BSED       |       2 ||         6 | BSTHRM     |       7 ||         7 | BLIS       |       4 ||         8 | BSCE       |       6 |+-----------+------------+---------+

3. tbl_部门

+---------+-------------------------------+| dept_id | full_name                     |+---------+-------------------------------+|       1 | Computer Education Department ||       2 | Basic Education Department    ||       3 | Mathematics Department        ||       4 | Humanities Department         ||       6 | Engineering Department        ||       7 | Management Department         |+---------+-------------------------------+

Answer 1

我认为你可以做这样的事情:

SELECT
    tbl_department.full_name as department,
    COUNT(DISTINCT tbl_students.stud_id) AS student
FROM
    tbl_department
    JOIN tbl_courses
        ON tbl_department.dept_id = tbl_courses.dept_id
    JOIN tbl_students
        ON tbl_courses.course_id = tbl_students.course
GROUP BY
    tbl_department.full_name