在MySQL中，如何选择空记录

Question

我有以下MySQL表结构：

mysql> desc customers;
+---------------------+-------------+------+-----+---------+-------+
| Field               | Type        | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| hash                | varchar(32) | NO   | PRI | NULL    |       |
| date_joined         | date        | NO   |     | NULL    |       |
| agent_code          | int(5)      | NO   | UNI |         |       |
+---------------------+-------------+------+-----+---------+-------+

mysql> desc persons;
+--------------------+-------------+------+-----+---------+-------+
| Field              | Type        | Null | Key | Default | Extra |
+--------------------+-------------+------+-----+---------+-------+
| agent_code         | int(5)      | NO   | UNI |         |       |
| team_id            | int(2)      | YES  |     | 0       |       |
| hash               | varchar(32) | NO   | PRI | NULL    |       |
+--------------------+-------------+------+-----+---------+-------+

mysql> desc teams;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  |     | NULL    |                |
| leader | varchar(32) | NO   | UNI | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

我希望能按团队生成一份销售报告。

我使用的SQL查询如下所示：

SELECT COUNT(`customers`.`agent_code) AS `customer_count`, `teams`.`name` 
FROM  `customers` 
JOIN  `persons` ON  `customers`.`agent_code` =  `persons`.`agent_code` 
JOIN  `teams` ON  `persons`.`team_id` =  `teams`.`id` 
GROUP BY  `teams`.`name`

它显示了以下信息：

+----------------+--------+
| customer_count | name   |
+----------------+--------+
|              3 | Team 1 |
+----------------+--------+

但是，我希望看到数据库中所有团队的" customer_count“，即使给定团队的customer_count为空(或零)。我的数据库中有15个团队，所以我希望看到如下内容：

+----------------+--------+
| customer_count | name   |
+----------------+--------+
|              3 | Team 1 |
|              0 | Team 2 |
|              0 | Team 3 |
|              0 | Team 4 |
+----------------+--------+

我尝试执行以下查询的一些变体，但我总是得到一个错误，指出OUTER JOIN的语法是不正确的，尽管我已经阅读了文档，而且它是正确的。

 SELECT COUNT(  `customers`.`agent_code` ) AS  `customer_count` ,  `teams`.`name` 
 FROM  `customers` 
 LEFT JOIN  `persons` ON  `customers`.`agent_code` =  `persons`.`agent_code` 
 LEFT OUTER JOIN  `teams` ON  `persons`.`team_id` =  `teams`.`id` 
 GROUP BY  `teams`.`name` 

如何更改当前查询以显示此类结果？

Answer 1

你弄错了主表是person -我建议你的主表是team

SELECT COUNT(`customers`.`id`) AS  `customer_count` ,  `teams`.`name` 
 FROM  `teams`
 JOIN  `persons` ON `persons`.`team_id` = `teams`.`id`
 LEFT JOIN `customers`  ON  `customers`.`agent_code` =  `persons`.`agent_code` 
 GROUP BY  `teams`.`name`

更新:如果您确实有空团队，那么您需要在persons上设置left join

SELECT COUNT(`customers`.`id`) AS  `customer_count` ,  `teams`.`name` 
 FROM  `teams`
 LEFT JOIN  `persons` ON `persons`.`team_id` = `teams`.`id`
 LEFT JOIN `customers`  ON  `customers`.`agent_code` =  `persons`.`agent_code` 
 GROUP BY  `teams`.`name`