我有4个表格,分别称为商店,用户,评论和评分。
我想获得相应商店的所有评论,包括已审查的用户详细信息以及该商店的总体评价。
我几乎完成了单个查询。但是问题在于,如果同一用户多次对该商店进行相同的评级,则将其视为单一评级。但是那个评分是正确的。
即
在此表中,user_id 3被评为shop_id 1 4次。因此计数为4,total_rating为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
当我运行此查询时,我得到了
但是我需要为user_id 3提供total_rating 17。
检查这个小提琴
你把DISTINCT
INsum( rating.rating) as total_rating,
这就是为什么结果(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
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句