如何修复SQL左连接查询中count()返回3而非预期1的问题？

你踩的这个坑我之前也掉进去过！核心问题出在同时对两张表做LEFT JOIN会产生笛卡尔积，直接导致计数被错误放大。

为什么会出现计数异常？ #

当你把dates表同时和cards、mymyv_cards做LEFT JOIN时，假设某天cards有3条符合条件的记录，mymyv_cards有1条符合条件的记录，两者关联后会生成3×1=3条重复的行数据。这时候count(mc.id)会把这3行都统计进去，所以结果显示为3，而不是实际的1条。而单独查询mymyv_cards时，因为没有和另一张表关联，不会产生这种重复行，所以计数正确。

两种可行的解决方案 #

方案1：先分别统计每张表的日数据，再关联日期表（推荐）

这种方式先对两张表按天聚合统计，避免了笛卡尔积的问题，性能也更优：

with recursive dates as (
 select curdate() as dte, 1 as lev
 union all
 select dte - interval 1 day, lev + 1
 from dates where lev < 7
),
cards_daily as (
    select 
        str_to_date(left(publication_date, 10), '%d-%m-%Y') as stat_date,
        count(id) as cards_count
    from cards
    where publicated = 1
    group by stat_date
),
mymyv_cards_daily as (
    select 
        str_to_date(left(publication_date, 10), '%d-%m-%Y') as stat_date,
        count(id) as mymyv_count
    from mymyv_cards
    where publicated = 1
    group by stat_date
)
select 
    DATE_FORMAT(d.dte, '%d') AS date,
    coalesce(c.cards_count, 0) AS cards, -- 用coalesce处理无数据的日期，显示0而非null
    coalesce(m.mymyv_count, 0) AS mymyv_cards
from dates d
left join cards_daily c on c.stat_date = d.dte
left join mymyv_cards_daily m on m.stat_date = d.dte
group by d.dte;

方案2：使用count(distinct)临时修正（适合快速验证）

如果只是想快速验证结果，可以给count加上distinct关键字，强制统计唯一的id：

with recursive dates as (
 select curdate() as dte, 1 as lev
 union all
 select dte - interval 1 day, lev + 1
 from dates where lev < 7
)
select 
    DATE_FORMAT(d.dte, '%d') AS date, 
    count(distinct c.id) AS cards, 
    count(distinct mc.id) AS mymyv_cards
from dates d
left join cards c on c.publicated = 1 and str_to_date(left(c.publication_date, 10), '%d-%m-%Y') >= d.dte and str_to_date(left(c.publication_date, 10), '%d-%m-%Y') < d.dte + interval 1 DAY
left join mymyv_cards mc on mc.publicated = 1 and str_to_date(left(mc.publication_date, 10), '%d-%m-%Y') >= d.dte and str_to_date(left(mc.publication_date, 10), '%d-%m-%Y') < d.dte + interval 1 day
group by d.dte;

不过这种方式本质上还是会产生笛卡尔积，只是通过去重修正了计数，数据量大时性能会受影响，所以更推荐方案1。

内容的提问来源于stack exchange，提问作者user12755870