MySQL中SUM()函数无法正常工作：使用DISTINCT的SUM()函数

Question

我有4个表，分别是商店(shops)、用户(users)、评论(review)和评分(rating)。

我想要获取对应商店的所有评论，包括评论用户的详细信息以及该商店的总体评分。

我已经几乎用单个查询完成了。但问题在于，如果同一用户对同一商店进行多次相同的评分，则被视为单个评分。但评分计数是正确的。

i.e

从这张表中可以看出，用户ID为3的用户对商店ID为1的商店进行了4次评分。因此，评分总数为4，总评分为17。

我的查询是

select review.comments, users.username, count(distinct rating.id) as rating_count,
sum(distinct rating.rating) as total_rating from users 
left join review on users.id = review.user_id and review.shop_id='1' 
left join rating on users.id = rating.user_id and rating.shop_id='1' 
where review.shop_id='1' or rating.shop_id='1' 
group by users.id, review.user_id, rating.user_id, review.id

当我运行这个查询时，我得到了：

但我需要用户ID为3的total_rating值为17。

请查看此fiddle。

- RaGu

---

如果用户对同一家店进行了多次评论，该怎么办？ - Abhishek Ginani

@Code-Monk 我们可以展示多个。就像我的例子一样，Good、Test Review都被同一个用户为同一家店铺所评论。 - RaGu

Answer 1

你在 sum(rating.rating) as total_rating 中加了 DISTINCT，这就是为什么结果是 12=17-5，因为它只会在计算总和时包含 5 一次。

 select review.comments, review.user_id, count(distinct rating.id) as rating_count,
    sum( rating.rating) as total_rating from users 
    left join review on users.id = review.user_id and review.shop_id='1' 
    left join rating on users.id = rating.user_id and rating.shop_id='1' 
    where review.shop_id='1' or rating.shop_id='1' 
    group by users.id, review.user_id, rating.user_id, review.id

这里是SQLFiddle

示例输出： 希望这可以帮到你

- Subin Chalil

---

请检查附加的 fiddle，它能正常工作吗？ - Subin Chalil

Answer 2

尝试这个 - 从 sum(rating.rating) 中删除 distinct。由于你使用了 sum(distinct rating.rating)，它忽略了用户3给商店1的5分评级。

select review.comments, users.username, count(distinct rating.id) as rating_count,
sum(rating.rating) as total_rating from users 
left join review on users.id = review.user_id and review.shop_id='1' 
left join rating on users.id = rating.user_id and rating.shop_id='1' 
where review.shop_id='1' or rating.shop_id='1' 
group by users.id, review.user_id, rating.user_id, review.id

- buchipper

Answer 3

首先：在 WHERE 子句中删除表中外连接的记录是没有意义的。使用 left join review ... 可以找到与表 review 匹配的记录，如果没有找到，则添加 null 值，因此我们保留用户记录。然后使用 where review.shop_id='1' 仅保留实际在 review 中找到记录的记录。因此，您正在放弃刚刚保留的记录。WHERE 子句使您的 LEFT OUTER JOIN 变为 INNER JOIN。

至于您的实际问题：这源于首先连接所有表，然后只尝试从结果记录中获取聚合。请在连接之前进行聚合。

select 
  rev.comments, 
  usr.username, 
  coalesce(rat.rating_count, 0) as rating_count,
  rat.total_rating 
from review rev
join users usr on users.id = review.user_id 
left join
(
  select user_id, shop_id, count(*) as rating_count, sum(rating) as total_rating
  from rating 
  group by user_id, shop_id
) rat on rat.user_id = usr.id and rat.shop_id = rev.shop_id
where rev.shop_id = 1 
group by rev.id;

- Thorsten Kettner

---

谢谢。它正在工作。但是查询有点复杂。我必须在CodeIgniter中完成这个。 - RaGu

你是对的，抱歉，在你的情况下不需要预聚合，因为这里没有重复项。（如果您将查询扩展到另一个表以获取数据，则可能会出现重复项。）我被你的两个“distinct”弄混了，它们都是多余的。 - Thorsten Kettner

你应该将那个外连接的东西整理一下。请注意我已经更改了连接顺序和分组子句，以使查询更加直观：你想要显示一个商店的评论，所以从review中选择。你想要同时显示它的用户数据，所以连接users。最后，如果有的话，你想要匹配商店和用户的评分，所以使用外连接rating。按照review.id进行分组，以便每个评论只有一行结果。 - Thorsten Kettner