MySQL 按国家和支付方式统计就诊与付款总额

2026-10-24支付小周

结论

不要直接用 patient_id 连接“就诊明细”和“付款明细”。同一患者在两张表中都可能有多条记录，连接后会形成多对多关系，导致付款金额被重复累计。

应先将就诊记录去重，保留“每位患者一行”；再按患者汇总付款记录；最后连接两份结果，并按国家统计。

推荐写法

MySQL 8.0 及以上版本可以使用 CTE：

WITH visited_patients AS (
    SELECT DISTINCT
        app.patient_id
    FROM appointments AS app
    WHERE app.company_id = 111111111
      AND app.clinic_id = 15
      AND app.patient_id > 0
      AND app.appointment_status = 4
      AND app.start_date >= '2022-05-01'
      AND app.end_date < '2022-06-01'
      AND app.status = 1
),
patient_payments AS (
    SELECT
        pay.patient_id,
        SUM(
            CASE
                WHEN pay.payment_method = 1
                THEN pay.payment_amount * pay.rate
                ELSE 0
            END
        ) AS cash_payments,
        SUM(
            CASE
                WHEN pay.payment_method = 3
                THEN pay.payment_amount * pay.rate
                ELSE 0
            END
        ) AS eft_payments,
        SUM(
            CASE
                WHEN pay.payment_method IN (2, 4)
                THEN pay.payment_amount * pay.rate
                ELSE 0
            END
        ) AS card_sales,
        SUM(
            CASE
                WHEN pay.payment_method = 5
                THEN pay.payment_amount * pay.rate
                ELSE 0
            END
        ) AS cheque_sales,
        SUM(
            CASE
                WHEN pay.payment_method = 6
                THEN pay.payment_amount * pay.rate
                ELSE 0
            END
        ) AS bond_sales
    FROM payments AS pay
    WHERE pay.payment_date >= '2022-05-01'
      AND pay.payment_date < '2022-06-01'
      AND pay.is_deleted = 0
    GROUP BY pay.patient_id
)
SELECT
    country.country_name,
    COUNT(*) AS total_unique_visits,
    ROUND(SUM(COALESCE(pp.cash_payments, 0)), 2) AS cash_payments,
    ROUND(SUM(COALESCE(pp.eft_payments, 0)), 2) AS eft_payments,
    ROUND(SUM(COALESCE(pp.card_sales, 0)), 2) AS card_sales,
    ROUND(SUM(COALESCE(pp.cheque_sales, 0)), 2) AS cheque_sales,
    ROUND(SUM(COALESCE(pp.bond_sales, 0)), 2) AS bond_sales
FROM visited_patients AS vp
INNER JOIN patients AS p
    ON p.id = vp.patient_id
INNER JOIN countries AS country
    ON country.id = p.p_country
LEFT JOIN patient_payments AS pp
    ON pp.patient_id = vp.patient_id
GROUP BY
    country.id,
    country.country_name
ORDER BY
    country.country_name;

原查询为什么会重复求和

假设某位患者有：

3 条符合条件的就诊记录；
2 条符合条件的付款记录。

直接按 patient_id 连接，会得到 3 × 2 = 6 行。两笔付款都会分别匹配该患者的 3 条就诊记录，因此每笔金额都会被累计 3 次。

原查询使用了：

COUNT(DISTINCT app.patient_id)

它可以消除重复的患者编号，所以唯一患者数仍然正确。但普通的 SUM() 会对连接后的每一行求和，不会自动识别重复的付款记录。

改写后的查询先将两侧数据整理成“每位患者一行”：

visited_patients 通过 DISTINCT patient_id 去除重复就诊；
patient_payments 通过 GROUP BY patient_id 汇总每位患者的付款；
两份结果再按 patient_id 连接时，关系是一对一，金额不会被放大。

MySQL 5.7 写法

如果数据库不支持 CTE，可以改用派生表：

SELECT
    country.country_name,
    COUNT(*) AS total_unique_visits,
    ROUND(SUM(COALESCE(pp.cash_payments, 0)), 2) AS cash_payments,
    ROUND(SUM(COALESCE(pp.eft_payments, 0)), 2) AS eft_payments,
    ROUND(SUM(COALESCE(pp.card_sales, 0)), 2) AS card_sales,
    ROUND(SUM(COALESCE(pp.cheque_sales, 0)), 2) AS cheque_sales,
    ROUND(SUM(COALESCE(pp.bond_sales, 0)), 2) AS bond_sales
FROM (
    SELECT DISTINCT app.patient_id
    FROM appointments AS app
    WHERE app.company_id = 111111111
      AND app.clinic_id = 15
      AND app.patient_id > 0
      AND app.appointment_status = 4
      AND app.start_date >= '2022-05-01'
      AND app.end_date < '2022-06-01'
      AND app.status = 1
) AS vp
INNER JOIN patients AS p
    ON p.id = vp.patient_id
INNER JOIN countries AS country
    ON country.id = p.p_country
LEFT JOIN (
    SELECT
        pay.patient_id,
        SUM(CASE WHEN pay.payment_method = 1
                 THEN pay.payment_amount * pay.rate ELSE 0 END) AS cash_payments,
        SUM(CASE WHEN pay.payment_method = 3
                 THEN pay.payment_amount * pay.rate ELSE 0 END) AS eft_payments,
        SUM(CASE WHEN pay.payment_method IN (2, 4)
                 THEN pay.payment_amount * pay.rate ELSE 0 END) AS card_sales,
        SUM(CASE WHEN pay.payment_method = 5
                 THEN pay.payment_amount * pay.rate ELSE 0 END) AS cheque_sales,
        SUM(CASE WHEN pay.payment_method = 6
                 THEN pay.payment_amount * pay.rate ELSE 0 END) AS bond_sales
    FROM payments AS pay
    WHERE pay.payment_date >= '2022-05-01'
      AND pay.payment_date < '2022-06-01'
      AND pay.is_deleted = 0
    GROUP BY pay.patient_id
) AS pp
    ON pp.patient_id = vp.patient_id
GROUP BY
    country.id,
    country.country_name
ORDER BY
    country.country_name;

注意事项

不要使用 `SUM(DISTINCT payment_amount)`

下面的写法无法可靠地解决重复求和问题：

SUM(DISTINCT pay.payment_amount * pay.rate)

它去重的是“金额值”，不是付款记录。如果两笔不同的付款金额恰好相同，其中一笔就会被错误排除。

日期范围建议采用左闭右开

如果 payment_date 是 DATETIME 类型，下面的条件通常只能包含 2022-05-31 00:00:00，可能漏掉当天其他时间产生的付款：

pay.payment_date <= '2022-05-31'

更稳妥的写法是：

pay.payment_date >= '2022-05-01'
AND pay.payment_date < '2022-06-01'

也应避免对索引列使用 DATE()，例如：

DATE(app.start_date) >= '2022-05-01'

直接比较原字段通常更有利于使用索引。

确认付款的业务归属范围

上述查询保留了原有逻辑：只要某位患者在指定诊所有符合条件的就诊记录，其当月所有符合条件的付款都会计入统计。

如果 payments 表中还有 company_id、clinic_id 或具体就诊单号等字段，应按照业务规则在付款子查询中补充相应条件。例如：

AND pay.company_id = 111111111
AND pay.clinic_id = 15

否则，同一患者在其他公司或诊所产生的付款也可能被纳入统计。