如何编写MySQL查询计算两表计数商并按年月关联展示

我来帮你搞定这个问题！你的核心问题是当前查询用逗号连接两个子查询，相当于做了笛卡尔积关联，会把第一个子查询的每一行和第二个子查询的每一行都组合，得到的结果完全不符合你想要的“每个年月对应一个计算值”的需求，还会漏掉那些只在其中一张表存在的年月数据。

优化后的查询方案 #

这里提供两种方案，你可以根据需求选择：

方案1：展示所有存在的年月（推荐）

这种方式能确保展示所有在两张表中出现过的年月，即使其中一张表在该年月没有数据：

WITH all_months AS (
    -- 从两张表中提取所有唯一的年月，自动去重
    SELECT DATE_FORMAT(`date`, "%M %Y") AS `Months`
    FROM first_table
    UNION
    SELECT DATE_FORMAT(`date`, "%M %Y") AS `Months`
    FROM second_table
)
SELECT 
    am.`Months` AS `Month and Year`,
    -- 处理空值和除以0的情况，避免报错或出现NULL结果
    COALESCE(a.count_one / NULLIF(b.count_two, 0), 0) AS final_count
FROM all_months am
-- 左连接第一个表的年月计数
LEFT JOIN (
    SELECT 
        COUNT(*) AS count_one,
        DATE_FORMAT(`date`, "%M %Y") AS `Months`
    FROM first_table
    -- 简化分组逻辑，直接按年月字符串分组即可，不用转成日期
    GROUP BY DATE_FORMAT(`date`, "%Y-%m")
) a ON am.Months = a.Months
-- 左连接第二个表的年月计数
LEFT JOIN (
    SELECT 
        COUNT(*) AS count_two,
        DATE_FORMAT(`date`, "%M %Y") AS `Months`
    FROM second_table
    GROUP BY DATE_FORMAT(`date`, "%Y-%m")
) b ON am.Months = b.Months
-- 按时间顺序排序，避免字符串排序导致的顺序混乱
ORDER BY STR_TO_DATE(am.`Months`, "%M %Y");

方案2：仅展示两张表都有数据的年月

如果你只需要保留那些两张表都有对应年月数据的记录，可以用内连接替代左连接：

SELECT 
    a.Months AS `Month and Year`,
    COALESCE(a.count_one / NULLIF(b.count_two, 0), 0) AS final_count
FROM (
    SELECT 
        COUNT(*) AS count_one,
        DATE_FORMAT(`date`, "%M %Y") AS `Months`
    FROM first_table
    GROUP BY DATE_FORMAT(`date`, "%Y-%m")
) a
-- 内连接确保只保留两张表都存在的年月
INNER JOIN (
    SELECT 
        COUNT(*) AS count_two,
        DATE_FORMAT(`date`, "%M %Y") AS `Months`
    FROM second_table
    GROUP BY DATE_FORMAT(`date`, "%Y-%m")
) b ON a.Months = b.Months
ORDER BY STR_TO_DATE(a.Months, "%M %Y");

关键细节说明 #

• 避免笛卡尔积：用JOIN ... ON关联相同的Months字段，确保每个年月只匹配一次，而不是生成所有组合的无效数据。
• 处理异常值：
  • NULLIF(b.count_two, 0)：如果第二个表的计数为0，将其转为NULL，避免出现除以0的报错。
  • COALESCE(..., 0)：如果除法结果为NULL（比如某张表没有该年月数据，或者除数为0），将结果转为0，保证输出的一致性。
• 正确排序：用STR_TO_DATE(am.Months, "%M %Y")把年月字符串转成日期类型排序，避免出现“April 2016”排在“January 2017”前面的字符串排序错误。

最终输出示例 #

执行上述查询后，你会得到符合预期的结果：

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