mysql - 尝试在 MySQL 中连接 3 个表

Question

我有三个连接的表。我几乎有了解决方案，但这里似乎出现了一个小问题。以下是声明:

SELECT items.item,
COUNT(ratings.item_id) AS total,
COUNT(comments.item_id) AS comments,
AVG(ratings.rating) AS rate
FROM `items`
LEFT JOIN ratings ON (ratings.item_id = items.items_id)
LEFT JOIN comments ON (comments.item_id = items.items_id)
WHERE items.cat_id = '{$cat_id}'  AND items.spam < 5
GROUP BY items_id ORDER BY TRIM(LEADING 'The ' FROM items.item) ASC;");

我有一个名为 items 的表，每个项目都有一个名为 items_id 的 id (注意它是复数)。我有一张针对每个项目的个人用户评论表，以及一个针对每个项目的评分表。 (最后两个有一个名为“item_id”的相应列)。

我只是想分别计算评论和评分总数(每个项目)。按照我上面的SQL语句的方式，它们是一个总数。

• 注意，总计是评分的总和。这是一个糟糕的命名方案，我需要修复!

更新:“总计”似乎计数正常，但是当我向“评论”表添加评论时，COUNT 函数会影响“评论”和“总计”，并且似乎等于组合输出。

Answer 1

问题是您正在计算所有 3 个连接表的结果。尝试:

SELECT i.item,
r.ratetotal AS total,
c.commtotal AS comments,
r.rateav AS rate
FROM items AS i
LEFT JOIN
    (SELECT item_id, 
    COUNT(item_id) AS ratetotal, 
    AVG(rating) AS rateav 
    FROM ratings GROUP BY item_id) AS r 
ON r.item_id = i.items_id
LEFT JOIN
    (SELECT item_id, 
    COUNT(item_id) AS commtotal 
    FROM comments GROUP BY item_id) AS c
ON c.item_id = i.items_id
WHERE i.cat_id = '{$cat_id}'  AND i.spam < 5
ORDER BY TRIM(LEADING 'The ' FROM i.item) ASC;");

在此查询中，我们让子查询正确进行计数，然后将该值发送到主查询并过滤结果。