如何在表关联时仅用首个满足条件的值计算合规发票金额总和

核心逻辑梳理 #

要达成需求，关键需满足两点：

• 仅筛选CallData中Source='referral'的通话记录
• 对每个手机号，仅保留发票日期晚于至少一条对应推荐通话日期的发票，再对金额求和

方案1：预聚合通话日期（高效优先） #

先对CallData按手机号聚合，获取每个手机号最早的推荐通话日期——只要发票日期晚于这个最早日期，就必然晚于至少一条推荐通话日期，完全匹配需求。这种方式大幅减少了后续关联的数据量，适合数据规模较大的场景：

SELECT 
    i.PhoneNumber,
    ROUND(SUM(i.Amount), 2) AS TotalAmount
FROM Invoice i
INNER JOIN (
    -- 预聚合每个手机号的最早推荐通话日期
    SELECT 
        PhoneNumber,
        MIN(CallDate) AS EarliestReferralCallDate
    FROM CallData
    WHERE Source = 'referral'
    GROUP BY PhoneNumber
) cd ON i.PhoneNumber = cd.PhoneNumber
WHERE i.InvoiceDate > cd.EarliestReferralCallDate
GROUP BY i.PhoneNumber;

方案2：EXISTS子查询（逻辑直观） #

直接通过子查询判断当前发票是否存在对应的推荐通话日期早于发票日期，逻辑更直白，适合需要明确验证每条发票合规性的场景：

SELECT 
    PhoneNumber,
    ROUND(SUM(Amount), 2) AS TotalAmount
FROM Invoice i
WHERE EXISTS (
    SELECT 1
    FROM CallData cd
    WHERE cd.PhoneNumber = i.PhoneNumber
      AND cd.Source = 'referral'
      AND cd.CallDate < i.InvoiceDate
)
GROUP BY PhoneNumber;

性能优化建议 #

为最大化查询效率，建议添加以下复合索引：

• 给CallData创建索引：CREATE INDEX idx_cd_phone_source_date ON CallData(PhoneNumber, Source, CallDate);
• 给Invoice创建索引：CREATE INDEX idx_inv_phone_date ON Invoice(PhoneNumber, InvoiceDate);

示例结果验证 #

运行上述SQL后，会得到符合需求的结果：

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