php – 用三个表行数MySQL

发布时间：2020-12-13 17:05:53 所属栏目：PHP教程来源：网络整理

导读：我的 MySQL DB看起来像这样 **table_schools**id | name1 | school_12 | school_2**table_classes**id | class | school_id1 | a | 12 | b | 13 | c | 24 | d | 25 | e | 2**table_students**id | name | class_id1 | Nick | 12 | Tom | 23 | Kevin | 34 | J

我的 MySQL DB看起来像这样

**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查询中执行此操作？如何？

解决方法

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!