mysql - 如何在连接表上查询 COUNT 并返回 count=0 和 count>0 的记录

Question

我有一个“产品”表和一个“评论”表。
我想编写一个查询来返回每个产品的评论的 COUNT 和 AVG。
并且如果没有评论，我希望它为 COUNT 和 AVG 返回 0/null。

产品表

+--------+----------+
| prodId | prodName |
+--------+----------+
|   1    |  apple   |
|   2    |  banana  |
|   3    |  cacao   |
+--------+----------+

审核表

+----------+----------+--------+
| reviewId |  prodId  | rating |
+----------+----------+--------+
|    1     |     1    |    1   |
|    2     |     1    |    1   |
|    3     |     2    |    5   |
|    4     |     2    |    5   |
+----------+----------+--------+

这就是我想要的结果:

+--------+----------+--------+-------+
| prodId | prodName |   avg  | count |
+--------+----------+--------+-------+
|   1    |  apple   |    1   |   2   |
|   2    |  banana  |    5   |   2   |
|   3    |  cacao   |   null |   0   |
+--------+----------+--------+-------+

我可以获得有评论的产品的 COUNT 和 AVG

SELECT p.prodid, p.prodname, avg(r.stars), count(r.productid)
FROM products p 
INNER JOIN reviews r ON p.productid=r.productid 
GROUP BY p.productid

+--------+----------+--------+-------+
| prodId | prodName |   avg  | count |
+--------+----------+--------+-------+
|   1    |  apple   |    1   |   2   |
|   2    |  banana  |    5   |   2   |
+--------+----------+--------+-------+

我还可以获得没有评论的产品的 COUNT 和 AVG

SELECT p.prodid, p.prodname, avg(r.stars), count(r.stars)
FROM products p
LEFT OUTER JOIN reviews r 
ON (p.productid=r.productid) WHERE r.productid IS NULL
GROUP BY p.productid

+--------+----------+--------+-------+
| prodId | prodName |   avg  | count |
+--------+----------+--------+-------+
|   3    |  cacao   |   null |   0   |
+--------+----------+--------+-------+

但我不知道MySql是否有一种方法来查询和统计一次查询中匹配的结果和统计不匹配的结果。

Answer 1

只需从第二个查询中删除 where 条件

SELECT p.prodid, p.prodname, avg(r.stars), count(r.stars)
FROM products p
LEFT OUTER JOIN reviews r 
ON p.productid=r.productid
GROUP BY p.productid,p.prodname