SQL INNER JOIN多JOBID匹配同一INVOICEID致重复的求和问题

重复统计的核心原因是多表关联链路产生了笛卡尔积行放大：

• 中间表INLines存储发票与作业的映射关系，当1个INVOICEID匹配2个不同JOBID时，INLines子查询会返回2条同INVOICEID、不同JOBID的记录
• 原查询先关联Jobs与INLines表，再关联Invoice表，同一张发票会因为匹配多个JOBID被重复计数，即使加distinct也无法解决聚合前的行放大问题，最终sum(totalBASE)会重复计算同一张发票的金额
• 原SQL存在多处冗余写法：子查询中同时写distinct和group by无实际去重意义，where条件重复写了和on条件完全一致的关联规则，外层聚合结果加distinct也无法修正重复计数的错误

要实现「同一张发票即使匹配多个JOBID也只统计一次金额」的需求，核心是先完成发票维度的金额聚合，再做作业维度的关联筛选，从根源上避免多对多关系进入金额计算环节：

1. 先从Invoice表聚合出每个发票ID对应的唯一金额、所属日期，从源头保证单张发票只有1条金额记录
2. 关联INLines和Jobs表筛选出符合匹配规则的有效发票ID，做去重处理，避免同个发票ID因为匹配多个JOBID出现多行
3. 最后把去重后的有效发票ID和预聚合的发票金额做关联汇总，彻底避免重复计数

SELECT
    SUM(inv.totalBASE) AS InvoiceTotal,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, inv.InvDate), 0) AS StatMonth
FROM
    -- 预聚合发票维度金额，保证单发票单条记录，从源头避免金额重复计算
    (
        SELECT
            AINVOICEID,
            SUM(totalBASE) AS totalBASE,
            InvDate
        FROM Invoice
        WHERE InvDate BETWEEN '2022-05-01' AND '2022-05-31'
        GROUP BY AINVOICEID, InvDate
    ) inv
INNER JOIN
    -- 筛选关联了目标作业的有效发票ID，去重避免同发票多JOBID导致行放大
    (
        SELECT DISTINCT
            il.ABINVOICEID
        FROM INLines il
        INNER JOIN Jobs j ON LEFT(il.ARLJOBID,5) = LEFT(j.JOBID,5)
    ) valid_inv ON inv.AINVOICEID = valid_inv.ABINVOICEID
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, inv.InvDate), 0)

• 如果后续需要按JOBID维度分组统计（比如统计每个作业对应发票金额），要提前明确多对多场景下的金额拆分规则，不能直接关联后sum，否则一定会出现总金额对账不平的问题
• 用left(字段,5)做关联条件会导致字段上的索引失效，数据量大时查询性能会很差，建议业务上如果固定取前5位做匹配，可以在表中新增计算列存储匹配值，再加关联索引
• 日期筛选建议用无歧义的标准格式（如'2022-05-01'），避免不同数据库区域设置把'05/01/2022'识别为1月5日

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