SUM()在MySQL中不起作用:具有DISTINCT的SUM()

问题描述

我有4个表，分别称为商店，用户，评论和评分.

I have 4 tables called shops, users, review and rating.

我想获得相应商店的所有评论，包括已审查的用户详细信息以及该商店的总体评分.

I want to get all reviews for the corresponding shop with reviewed user details and also overall rating for that shop.

我几乎完成了单个查询.但是问题是，如果同一用户多次对该商店进行相同的评级，则将其视为单一评级.但是那个评分数字是正确的.

I have done almost with the single query. But the problem is if the shop has same rating for multiple times by same user its consider as single rating. But that rating count was correct.

即

此表中的user_id 3被评为shop_id 1 4次.因此，计数为4，total_rating为17.

from this table user_id 3 was rated shop_id 1 as 4 times. So the count is 4 and total_rating is 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

运行此查询时，我得到了

When I run this query I got

但是我需要user_id 3的total_rating 17.

But I need total_rating 17 for user_id 3..

选中此小提琴

推荐答案

您将DISTINCT放在sum( rating.rating) as total_rating,中，这就是为什么结果( 12 = 17-5 )，因为它将包括5计算总和时只有一次.

You put DISTINCT IN sum( rating.rating) as total_rating, thats why the result(12=17-5), since it will include 5 only once while computing sum.

 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

样本输出:希望这会有所帮助

Sample Output :Hope this helps

这篇关于SUM()在MySQL中不起作用:具有DISTINCT的SUM()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！