【紧急】【Mysql】为什么多次leftjoin后使用聚合函数count统计数据就不准确了呢？？跪求大神指点！

Question

先上sql

SELECT
    a.id venueId,
    a.venue_name,
    a. STATUS venueStatus,
    COUNT(b.id) venueCourseCount
FROM
    t_venue a
LEFT JOIN t_venue_course b ON b.venue_id = a.id
GROUP BY
    a.id;

只有a和b进行左外连接 查出来的数据是

如图所示，这是可以看到venueId为4的venueCourseCount为10

然后我改一下sql

SELECT
    a.id venueId,
    a.venue_name,
    a. STATUS venueStatus,
    COUNT(b.id) venueCourseCount
FROM
    t_venue a
LEFT JOIN t_venue_course b ON b.venue_id = a.id
LEFT JOIN t_course_info c ON c.venue_course_id = b.id
GROUP BY
    a.id;

这时 b表和c表又进行一次左外连接，但是venueId为4的venueCourseCount变成16了 这是什么情况，我都groupby分组了啊 求大神搭救

Answer 1

Because the second condition is not one-to-one, this is the only explanation. If necessary, you can use distinct in count or add a join condition of the second table to ensure that the records in the three tables are one-to-one.