MySQL 三油品销售表按天关联分组 缺失数据补零实现咨询

你之前直接对三张原始销售表做LEFT JOIN无法得到正确结果，核心存在两个问题：

• 单张销售表存在同一天多条销售记录时，多表直接关联会触发笛卡尔积膨胀，导致最终聚合值被错误放大（比如柴油表某天2条记录、辛烷表同天2条记录，直接关联会生成4行交叉数据，求和后数值是实际值的2倍）
• 没有统一的全量日期基准，无法覆盖“某天单张表无数据、甚至三张表都无数据”的场景，也就无法实现无匹配时补0的要求

整体分三步落地，完全匹配你给出的表结构和输出要求：

1. 构建覆盖统计范围的全量日期基准，确保所有需要展示的日期都在基准集中
2. 对三张销售表单独按日期做预聚合，把同一天的多条销售记录汇总为单日单行的结果，从根源上避免多表关联时的笛卡尔膨胀
3. 用日期基准分别左关联三个预聚合结果集，通过COALESCE把无匹配的空值统一替换为0

完整查询SQL #

-- 按需调整统计的起止日期范围
WITH RECURSIVE date_range AS (
    -- 统计起始日期
    SELECT STR_TO_DATE('15/01/2021', '%d/%m/%Y') AS date
    UNION ALL
    -- 逐天累加直到统计结束日期
    SELECT DATE_ADD(date, INTERVAL 1 DAY)
    FROM date_range
    WHERE date < STR_TO_DATE('20/01/2021', '%d/%m/%Y')
),
-- 柴油表单独按日期预聚合
diesel_agg AS (
    SELECT 
        STR_TO_DATE(date, '%d/%m/%Y') AS sale_date,
        SUM(sell_quantity) AS sum_diesel_qty,
        SUM(profit) AS sum_diesel_profit
    FROM diesel_data
    GROUP BY STR_TO_DATE(date, '%d/%m/%Y')
),
-- 辛烷表单独按日期预聚合
octane_agg AS (
    SELECT 
        STR_TO_DATE(date, '%d/%m/%Y') AS sale_date,
        SUM(sell_quantity) AS sum_octane_qty,
        SUM(profit) AS sum_octane_profit
    FROM octane_data
    GROUP BY STR_TO_DATE(date, '%d/%m/%Y')
),
-- 美孚表单独按日期预聚合
mobil_agg AS (
    SELECT 
        STR_TO_DATE(date, '%d/%m/%Y') AS sale_date,
        SUM(sell_quantity) AS sum_mobil_qty,
        SUM(profit) AS sum_mobil_profit
    FROM mobil_data
    GROUP BY STR_TO_DATE(date, '%d/%m/%Y')
)
-- 关联计算最终结果，空值统一补0
SELECT 
    DATE_FORMAT(dr.date, '%d/%m/%Y') AS date,
    COALESCE(da.sum_diesel_qty, 0) AS `sum(sell_quantity)diesel`,
    COALESCE(da.sum_diesel_profit, 0) AS `sum(profit)diesel`,
    COALESCE(oa.sum_octane_qty, 0) AS `sum(sell_quantity)octane`,
    COALESCE(oa.sum_octane_profit, 0) AS `sum(profit)octane`,
    COALESCE(ma.sum_mobil_qty, 0) AS `sum(sell_quantity)mobil`,
    COALESCE(ma.sum_mobil_profit, 0) AS `sum(profit)mobil`
FROM date_range dr
LEFT JOIN diesel_agg da ON dr.date = da.sale_date
LEFT JOIN octane_agg oa ON dr.date = oa.sale_date
LEFT JOIN mobil_agg ma ON dr.date = ma.sale_date
ORDER BY dr.date;

可选调整 #

• 如果你的表中date字段本身就是DATE/DATETIME类型，直接去掉所有STR_TO_DATE的转换逻辑即可，减少多余的类型转换开销
• 如果不需要展示完全没有任何销售记录的日期（比如示例中17号、20号这类全空的日期），可以把递归生成连续日期的CTE替换为三表日期收集版本，仅保留三张表中实际出现过的日期，不需要手动指定统计起止范围：

-- 替换原date_range CTE的写法
WITH date_range AS (
    SELECT DISTINCT STR_TO_DATE(date, '%d/%m/%Y') AS date FROM diesel_data
    UNION
    SELECT DISTINCT STR_TO_DATE(date, '%d/%m/%Y') AS date FROM octane_data
    UNION
    SELECT DISTINCT STR_TO_DATE(date, '%d/%m/%Y') AS date FROM mobil_data
)

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