MySQL按国家和支付方式统计就诊支付数据时左连接重复求和问题

原查询直接通过patient_id关联预约表与支付表，当同一患者在统计周期内存在多条符合条件的预约记录时，该患者的每条支付记录会被重复关联（重复次数等于该患者符合条件的预约数），最终SUM计算支付总额时就会出现金额重复累加的错误。

将独立就诊量统计、支付金额统计拆分为两个独立的聚合逻辑，先分别按国家维度完成聚合，再关联结果，从根源上避免一对多关联造成的数据行膨胀。
修正后的SQL如下：

WITH visit_stats AS (
    -- 先统计各国符合条件的独立就诊患者数
    SELECT
        p.p_country,
        COUNT(DISTINCT app.patient_id) AS total_unique_visits
    FROM appointments app 
    INNER JOIN patients p ON p.id = app.patient_id
    WHERE 
        app.company_id = 111111111 
        AND app.clinic_id = 15 
        AND app.patient_id > 0 
        AND app.appointment_status = 4 
        AND DATE(app.start_date) >= '2022-05-01' 
        AND DATE(app.end_date) <= '2022-05-31' 
        AND app.status = 1
    GROUP BY p.p_country
),
payment_stats AS (
    -- 再统计各国对应周期内各支付方式的总金额，不和预约记录直接关联避免重复
    SELECT
        p.p_country,
        ROUND(SUM(IF(pay.payment_method = 1, pay.payment_amount * pay.rate, 0)),2) AS cash_payments,
        ROUND(SUM(IF(pay.payment_method = 3, pay.payment_amount * pay.rate, 0)),2) AS eft_payments,
        ROUND(SUM(IF(pay.payment_method IN (2,4), pay.payment_amount * pay.rate, 0)),2) AS card_sales,
        ROUND(SUM(IF(pay.payment_method = 5, pay.payment_amount * pay.rate, 0)),2) AS cheque_sales,
        ROUND(SUM(IF(pay.payment_method = 6, pay.payment_amount * pay.rate, 0)),2) AS bond_sales
    FROM payments pay
    INNER JOIN patients p ON p.id = pay.patient_id
    WHERE
        pay.payment_date >= '2022-05-01' 
        AND pay.payment_date <= '2022-05-31' 
        AND pay.is_deleted = 0
    GROUP BY p.p_country
)
SELECT
    country.country_name,
    vs.total_unique_visits,
    ps.cash_payments,
    ps.eft_payments,
    ps.card_sales,
    ps.cheque_sales,
    ps.bond_sales
FROM visit_stats vs
INNER JOIN countries country ON country.id = vs.p_country
LEFT JOIN payment_stats ps ON ps.p_country = vs.p_country
ORDER BY country.country_name

补充说明 #

• 该写法下就诊统计、支付统计逻辑完全独立，不存在多表关联导致的行重复，支付金额只会计算一次，不会随患者就诊次数被累加多次。
• 如果业务要求支付记录必须和对应就诊绑定（而非统计患者当月所有支付），只需要在payment_stats模块中增加支付表与预约表的关联条件（通常是pay.appointment_id = app.id），先完成支付与单条对应预约的匹配再做聚合，同样可以避免重复统计问题。

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