mysql - 获取 SQL 查询以在 3 个表中为空计数结果打印 0

Question

我正在尝试获取 SQL 查询以提供计数结果，但我需要结果包括计数为 0 的行。我找到的解决方案是使用 IFNULL(COUNT (*), 0) 代替 COUNT(*) 但这对结果没有影响。我还尝试使用 LEFT JOIN，但如果我尝试放入这些，SQL 会给我一个语法错误。这是我的 table 设置

用户

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| UserID      | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| firstName   | varchar(15)  | NO   |     | NULL    |                |
| lastName    | varchar(15)  | NO   |     | NULL    |                |
| Protocol    | varchar(10)  | NO   |     | NULL    |                |
| Endpoint    | varchar(50)  | NO   |     | NULL    |                |
| UsergroupID | mediumint(9) | NO   | MUL | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

订阅

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| SubscriptionID | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| TopicID        | mediumint(9) | NO   | MUL | NULL    |                |
| UserID         | mediumint(9) | NO   | MUL | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

话题

+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| TopicID  | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| Name     | varchar(50)  | NO   |     | NULL    |                |
| FBName   | varchar(30)  | YES  |     | NULL    |                |
| FBToken  | varchar(255) | YES  |     | NULL    |                |
| TWName   | varchar(10)  | YES  |     | NULL    |                |
| TWToken  | varchar(50)  | YES  |     | NULL    |                |
| TWSecret | varchar(50)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

我尝试获取 COUNT 的 SQL 查询是:

SELECT Topic.TopicID as ID, Topic.Name AS TopicName, COUNT(*) AS numSubscriptions
FROM User, Topic, Subscription
WHERE Subscription.UserID = User.UserID
  AND Subscription.TopicID = Topic.TopicID
GROUP BY Topic.TopicID;

我尝试用 IFNULL(COUNT(*), 0) 替换 COUNT(*) 并且我尝试替换 User,Topic,使用 User JOIN Subscription JOIN Topic 进行订阅，我还尝试了 User LEFT JOIN Subscription LEFT JOIN Topic，但出现了 SQL 错误。

我得到的输出是:

+----+-----------+------------------+
| ID | TopicName | numSubscriptions |
+----+-----------+------------------+
|  2 | test      |                2 |
|  3 | test2     |                1 |
+----+-----------+------------------+

我需要得到

+----+-----------+------------------+
| ID | TopicName | numSubscriptions |
+----+-----------+------------------+
|  2 | test      |                2 |
|  3 | test2     |                1 |
|  4 | test3     |                0 |
+----+-----------+------------------+

Answer 1

默认情况下，外部连接是从左到右。所以，诀窍是从主题开始:

SELECT Topic.TopicID as ID, Topic.Name AS TopicName,
  COUNT(User.UserID) AS numSubscriptions
FROM Topic
LEFT JOIN Subscription
  ON Subscription.TopicID = Topic.TopicID
JOIN User
  ON User.UserID = Subscription.UserID
GROUP BY Topic.TopicID

这允许每个用户有多个订阅，并且要求用户记录存在才能被计入计数。

COUNT(NULL)评估为 0 , 因此没有相应订阅和用户记录的任何主题记录将显示为 0 .

如果不关心用户记录是否存在，可以简化为:

SELECT Topic.TopicID as ID, Topic.Name AS TopicName,
  COUNT(Subscription.TopicID) AS numSubscriptions
FROM Topic
LEFT JOIN Subscription
  ON Subscription.TopicID = Topic.TopicID
GROUP BY Topic.TopicID