MySQL:没有匹配行时的 AVG

Question

我正在创建一个网站，用户可以在其中对项目(例如书籍)进行评分，从 1 星到 5 星。我创建了一个 MySQL 数据库和一个表，用于存储每个评级的信息(itemid、userid、 ratingValue)。

每个项目都有一个专门的网页，我想在此页面上添加以下信息:(1) 评分数量和 (2) 平均评分。我认为这最好是在有风景的情况下完成。在考虑在 View 中使用 MySQL 查询时，我想到:

按 ItemID 从评论组中选择 ItemID、COUNT( ratingValue)、IFNULL(AVG( ratingValue),0)

问题:如果某个项目尚未评级，它将不会出现在查询中。我想要的是所有项目都出现在查询结果( View )中，如果有一个项目没有评论，它应该显示有 0 条评论并且平均评分是某个任意值的信息，比方说0.

来自official documentation ,

AVG() returns NULL if there were no matching rows

因此我尝试使用 IFNULL() 函数。然而，这不起作用。例如，在包含项目 1,2,3,4,5 的数据库中，其中项目 5 没有评论，我将得到:

ItemID  COUNT(Rating)   IFNULL(AVG(ratingValue),0)1           4                4.00002           2                4.00003           2                3.50004           3                5.0000

关于如何克服这个问题有什么想法吗？

Answer 1

您必须有另一个表来列出您的所有商品(无论它们是否有评论)，并将该表左连接到您的评论表。如果没有这一点，就无法仅从评论表中推断出哪些项目可能存在没有评论。

所以类似:

SELECT
    i.itemID AS itemID,
    COUNT(r.rating) AS ratingCount,
    AVG(ratingValue) AS averageRating
FROM items AS i
LEFT JOIN reviews AS r
  ON i.itemID = r.itemID
GROUP BY i.itemID

这应该给出如下结果:

ItemID  ratingCount   averageRating
1       4             4.0000
2       2             4.0000
3       2             3.5000
4       3             5.0000
5       0             NULL