php - 三张表的行数MySQL

Question

我的 MySQL 数据库是这样的

**table_schools**
id | name
1  | school_1
2  | school_2

**table_classes**
id | class | school_id
1  | a     | 1
2  | b     | 1
3  | c     | 2
4  | d     | 2
5  | e     | 2

**table_students**
id | name  | class_id
1  | Nick  | 1
2  | Tom   | 2
3  | Kevin | 3
4  | Jane  | 4
5  | Mark  | 5
6  | Tim   | 5
7  | Lynn  | 5

我想要这样的输出:

school_name | class_count | student_count
school_1    | 2           | 2
school_2    | 3           | 5

有没有办法在一个 sql 查询中做到这一点？以及如何？

Answer 1

SELECT  s.name, COUNT(DISTINCT c.id) AS classes, COUNT(st.id) AS students
FROM    table_schools s
LEFT JOIN
        table_classes c
ON      c.school_id = s.id
LEFT JOIN
        table_students st
ON      st.class_id = c.id
GROUP BY
        s.id