MySQL两表关联统计count值异常的问题及修复方案

问题根源 #

1. WHERE条件破坏LEFT JOIN特性：你添加的WHERE tbl1.preferredDay = tbl2.preferredDay会过滤掉所有tbl2无匹配的行，直接把LEFT JOIN变成了INNER JOIN，因此永远出不来count_2=0的结果。
2. JOIN导致重复行统计错误：LEFT JOIN后，如果tbl2有多条匹配tbl1的记录，tbl1的行会被重复输出，此时COUNT(tbl1.preferredDay)统计的是重复后的行数，自然和COUNT(tbl2.preferredDay)结果一致。

修复方案 #

方案1：先分组统计再关联（推荐，性能更优）

先分别对两张表按preferredDay统计计数，再通过LEFT JOIN关联，避免JOIN产生重复行：

SELECT 
    t1.preferredDay,
    COALESCE(t1.count_1, 0) AS count_1,
    COALESCE(t2.count_2, 0) AS count_2
FROM (
    -- 统计tblschedule各日期的行数
    SELECT preferredDay, COUNT(*) AS count_1
    FROM tblschedule
    GROUP BY preferredDay
) t1
LEFT JOIN (
    -- 统计tblappointments各日期的行数
    SELECT preferredDay, COUNT(*) AS count_2
    FROM tblappointments
    GROUP BY preferredDay
) t2 ON t1.preferredDay = t2.preferredDay;

这里COALESCE用来把tbl2无匹配时的NULL转为0，符合你期望的输出格式。

方案2：基于原结构修改（需用唯一主键去重）

如果不想用子查询，可以保留LEFT JOIN，但用COUNT(DISTINCT 唯一键)避免重复统计tbl1的行数，同时去掉破坏LEFT JOIN的WHERE条件：

SELECT 
    tbl1.preferredDay,
    COUNT(DISTINCT tbl1.id) AS count_1, -- 替换为tbl1的实际唯一主键字段
    COUNT(tbl2.preferredDay) AS count_2
FROM tblschedule AS tbl1
LEFT JOIN tblappointments AS tbl2 ON tbl1.preferredDay = tbl2.preferredDay
GROUP BY tbl1.preferredDay;

COUNT(tbl2.preferredDay)会自动忽略tbl2无匹配时的NULL值，直接返回0。

内容的提问来源于stack exchange，提问作者D'CODER