mysql - 我的查询缺少一行包含零的行

Question

我搜索了此问题的答案，但没有找到针对此特定问题的答案。我正在制作一个数据库来存储教师创建的类(class)，以及每个类(class)注册的学生的查找表。我想运行一个查询来显示每个老师的类(class)和学生人数。正如您在下面的数据中看到的，老师有两个类(class)，学生人数为零，但只有其中一个类(class)在查询中返回。

查询:

SELECT `c`.`ClassID` AS `id`,
  `c`.`Class_Name` AS `name`,
  `c`.`Class_Code` AS `code`,
  COUNT(`e`.`EnrollID`) AS `count`

FROM `CMP_Classes` `c`

LEFT OUTER JOIN `CMP_Student_Enrollment` `e`
  ON `c`.`ClassID` = `e`.`Enroll_ClassID`
  AND `Class_Teacher` = 1

GROUP BY `e`.`EnrollID`

数据:

CMP_Classes 表:

ClassID | Class_Name                  | Class_Code | Class_Teacher
1       | Mr. Jones' 1st Period Class | QYTNPCGK   | 1
2       | Mr. Jones' 2nd Period Class | HZWNDZPM   | 1
3       | Pizza                       | RRCXQNNE   | 9
4       | Mr. Jones' 3rd Period Class | NFLBXFEQ   | 1

CMP_Student_Enrollment 表:

EnrollID | Enroll_Student | Enroll_ClassID
3        | 2              | 1

查询结果:

id | name                        | code     | count
2  | Mr. Jones' 2nd Period Class | HZWNDZPM | 0
1  | Mr. Jones' 1st Period Class | QYTNPCGK | 1

因此，正如您所看到的，“琼斯先生的第三节课”没有行，但应该有。

Answer 1

您需要对 select 中未聚合的列进行分组:

SELECT c.ClassID AS id, c.Class_Name AS name, c.Class_Code AS code,
       COUNT(e.EnrollID) AS `count`
FROM CMP_Classes c LEFT OUTER JOIN
     CMP_Student_Enrollment e
     ON c.ClassID = e.Enroll_ClassID
WHERE c.Class_Teacher = 1
GROUP BY c.ClassID, c.Class_Name, c.Class_Code;

查询中的所有反引号都会增加阅读和书写的难度。