php - MYSQL 连接三张表

Question

我有一个简单的多学校管理系统，我正在尝试获取特定学校的教师总数和学生总数。我的表结构如下:

        teachers
--------------------------
id | schoolid | Name | etc...
--------------------------
1  |    1     | Bob  |
2  |    1     | Sarah|
3  |    2     | John |


        students
--------------------------
id | schoolid | Name | etc... 
--------------------------
1  |    1     | Jack |
2  |    1     | David|
3  |    2     | Adam |

        schools
--------------------------
id |      Name       | etc...
---------------------------
 1 | River Park High |
 2 | Stirling High   |

我可以通过以下查询计算出所有教师:

SELECT COUNT(a.id) AS `totalteachers` 
FROM teachers a 
LEFT JOIN schools b ON a.schoolid = b.id WHERE b.id = '1'

同样，我可以使用以下查询来计算教师的数量:

SELECT COUNT(a.id) AS `totalstudents` 
FROM students a 
LEFT JOIN schools b ON a.schoolid = b.id WHERE b.id = '1'

然而，我正在努力尝试将这两个查询结合起来以获得如下简单的结果:

totalstudents | totalteachers
--------------------------------
       2      |      2

我尝试了以下方法:

SELECT COUNT(a.id) as `totalteachers`, COUNT(c.id) as `totalstudents` 
FROM teachers a 
LEFT JOIN schools b ON a.schoolid = b.id 
LEFT JOIN students c ON c.schoolid=b.id WHERE b.id = '5'

Answer 1

你可以这样做

SELECT 
  id, name, s.total AS totalstudents, t.total AS totalteachers
FROM schools
  JOIN (SELECT schoolid, COUNT(id) AS total FROM teachers GROUP BY schoolid)
    AS t ON t.schoolid = id        
  JOIN (SELECT schoolid, COUNT(id) AS total FROM students GROUP BY schoolid)
    AS s ON s.schoolid = id

然后你可以添加 where id = 2 或任何限制学校的内容。