使用MySQL Join关联两张表并对指定列分组求和的问题求助

云上老周

2026-10-9

问题原因

关联条件不匹配：两张表的聚合维度都是product_name、start_date、pack_size+关联键contract_id/contract_id_fk，但你只使用contract_id_fk = c.contract_id作为唯一关联条件，会触发一对多匹配产生笛卡尔积，导致求和结果重复放大
聚合顺序错误：你先做表关联再执行外层GROUP BY聚合，关联产生的重复行已经污染了原始数据，再聚合得到的结果自然错误
子查询字段不符合GROUP BY规范：子查询中SELECT了contract_id_fk、monthlydates等不在GROUP BY列表中的字段，得到的是不确定的随机值，进一步导致关联错误

正确写法

先分别完成两张表的分组聚合，再用全部聚合维度做关联，代码如下：

SELECT 
    c_agg.contract_id,
    c_agg.product_name,
    c_agg.pack_size,
    c_agg.sum_qty,
    c_agg.contract_prod,
    c_agg.end_date,
    c_agg.start_date,
    cb_agg.mysum,
    cb_agg.monthlydates
FROM 
    -- 先聚合contracts表
    (
        SELECT  
            contract_id, 
            product_name, 
            pack_size, 
            SUM(qty) AS sum_qty, 
            contract_prod, 
            end_date,
            start_date
        FROM contracts 
        GROUP BY contract_id, product_name, start_date, pack_size, contract_prod, end_date
    ) AS c_agg
LEFT JOIN 
    -- 再聚合contracts_balance表
    (
        SELECT 
            contract_id_fk, 
            product_name, 
            pack_size, 
            start_date,
            MAX(monthlydates) AS monthlydates,
            SUM(balance_aftermonth) AS mysum
        FROM contracts_balance 
        WHERE MONTH(monthlydates) = 3
        GROUP BY contract_id_fk, product_name, start_date, pack_size
    ) AS cb_agg
    -- 关联条件带上所有共同的分组维度，避免匹配错误
    ON c_agg.contract_id = cb_agg.contract_id_fk
    AND c_agg.product_name = cb_agg.product_name
    AND c_agg.pack_size = cb_agg.pack_size
    AND c_agg.start_date = cb_agg.start_date;