mysql - 将左连接与 GROUP_CONCAT 结合使用时，SUM() 返回错误值

Question

我有 3 个表，分别是商店、服务和评级。

商店有

Services 为商店提供了不止一项服务

Rating 有店铺的评分

我需要在单个查询中获取带有服务的商店详细信息以及评级。

我已经完成了这个查询

select shops.*, count(distinct rating.id) as rating_count, 
sum(rating.rating) as total_rating,
GROUP_CONCAT(distinct services.servicename SEPARATOR " <=> ") as servicename
from shops
LEFT JOIN rating on rating.shop_id = shops.id
LEFT JOIN services on services.shop_id = shops.id
group by shops.id

返回

但是 shop1 有 total_rating 21。但它显示 42。

我需要正确的总评分以及该商店的所有服务。

检查这个fiddle

Answer 1

select q1.id, q1.shopname, q1.rating_count, q1.total_rating,
GROUP_CONCAT(distinct services.servicename SEPARATOR " <=> ") as servicenamefrom
FROM
(select shops.*, count(distinct rating.id) as rating_count, 
sum(rating.rating) as total_rating
from shops
LEFT JOIN rating on rating.shop_id = shops.id
group by shops.id) q1
LEFT JOIN services on services.shop_id = q1.id
GROUP BY q1.id,  q1.shopname, q1.rating_count, q1.total_rating

http://sqlfiddle.com/#!9/ece82/20

要了解您的问题，请运行 select w/o group by 并检查您在加入之后但在聚合之前获得的行。您将发布重复计算的来源。