PAYATHON 2026

MySQL SELECT 按 payment method 和 status 汇总销售额

支付小周

结论

这里应使用“条件聚合”，也就是把 CASE 放在 SUM() 内部。还需要先按 basket_id 汇总付款金额，避免 basket_payment 中存在多条记录时重复累计 amount_due。

假设付款方式 ID 如下：

  • 1：CASH
  • 2：Credit/Debit Card
  • 4：Cash on Delivery

查询可以写成：

WITH payment_totals AS (
    SELECT basket_id,
           payment_method_id,
           SUM(amount_paid) AS amount_paid
    FROM basket_payment
    GROUP BY basket_id, payment_method_id
)
SELECT DATE(bv.sale_time) AS sale_date,

       ROUND(SUM(
           CASE
               WHEN p.payment_method_id = 1
                    AND bt.payment_status IN ('paid', 'partial')
                   THEN COALESCE(p.amount_paid, 0)

               WHEN p.payment_method_id = 4
                    AND bt.payment_status = 'paid'
                   THEN COALESCE(p.amount_paid, 0)

               ELSE 0
           END
       ), 2) AS total_cash_sales,

       ROUND(SUM(
           CASE
               WHEN p.payment_method_id = 1
                    AND bt.payment_status IN ('due', 'partial')
                   THEN GREATEST(
                       bv.amount_due - COALESCE(p.amount_paid, 0),
                       0
                   )

               WHEN p.payment_method_id = 4
                    AND bt.payment_status = 'due'
                   THEN GREATEST(
                       bv.amount_due - COALESCE(p.amount_paid, 0),
                       0
                   )

               ELSE 0
           END
       ), 2) AS total_credit_sales,

       ROUND(SUM(
           CASE
               WHEN p.payment_method_id = 4
                    AND bt.payment_status IN ('paid', 'due')
                   THEN bv.amount_due
               ELSE 0
           END
       ), 2) AS total_cash_on_delivery_sales,

       ROUND(SUM(
           CASE
               WHEN p.payment_method_id = 2
                    AND bt.payment_status = 'paid'
                   THEN COALESCE(p.amount_paid, 0)
               ELSE 0
           END
       ), 2) AS total_card_sales

FROM basket_amount_due AS bv
JOIN basket AS bt
  ON bt.basket_id = bv.basket_id
LEFT JOIN payment_totals AS p
  ON p.basket_id = bv.basket_id
WHERE bv.sale_time >= CURDATE()
  AND bv.sale_time < CURDATE() + INTERVAL 1 DAY
GROUP BY DATE(bv.sale_time);

按照题目提供的数据，结果应为：

total_cash_salestotal_credit_salestotal_cash_on_delivery_salestotal_card_sales
3160.001570.501688.402070.00

计算逻辑

total_cash_sales 是实际收到的现金：

1170.00 + 1000.00 + 990.00 = 3160.00

具体包括：

  • CASH 且状态为 paid 时，计入已付款金额。
  • CASH 且状态为 partial 时，只计入已经支付的部分。
  • Cash on Delivery 且状态为 paid 时，计入已付款金额。

total_credit_sales 实际统计的是尚未收取的金额：

756.60 + (1115.50 - 1000.00) + 698.40 = 1570.50

部分付款订单按下面的公式计算：

未付款金额 = amount_due - amount_paid

total_cash_on_delivery_sales 按题目规则统计 Cash on Delivery 的订单总额，不区分当前状态是 paid 还是 due：

990.00 + 698.40 = 1688.40

total_card_sales 统计银行卡已支付的金额：

2070.00

为什么原查询得不到正确结果

原查询使用了下面的结构：

CASE
    WHEN ...
    THEN SUM(amount_due)
    ELSE 0
END

这种写法先按付款方式分组，再判断当前分组属于哪种类型，因此每个 payment_method_id 都会返回一行，无法直接得到每天一行、包含多个汇总列的结果。

条件聚合应该写成：

SUM(
    CASE
        WHEN 条件 THEN 金额
        ELSE 0
    END
)

每条销售记录先根据条件确定计入多少金额，再由 SUM() 统一汇总。

原查询中的：

GROUP BY p.payment_method_id

还会把 CASH、Card 和 Cash on Delivery 拆成多行。目标结果是每天一行，因此只需要：

GROUP BY DATE(bv.sale_time)

日期条件的写法

不建议这样写：

WHERE DATE(bv.sale_time) = CURDATE()

对 sale_time 调用 DATE()，可能导致普通的 sale_time 索引无法用于范围查找。使用半开区间更合适：

WHERE bv.sale_time >= CURDATE()
  AND bv.sale_time < CURDATE() + INTERVAL 1 DAY

查询指定日期时，可以写成：

WHERE bv.sale_time >= '2021-09-18'
  AND bv.sale_time <  '2021-09-19'

MySQL 5.7 写法

MySQL 5.7 不支持 CTE，可以把付款汇总改为派生表：

SELECT DATE(bv.sale_time) AS sale_date,

       ROUND(SUM(
           CASE
               WHEN p.payment_method_id = 1
                    AND bt.payment_status IN ('paid', 'partial')
                   THEN COALESCE(p.amount_paid, 0)
               WHEN p.payment_method_id = 4
                    AND bt.payment_status = 'paid'
                   THEN COALESCE(p.amount_paid, 0)
               ELSE 0
           END
       ), 2) AS total_cash_sales,

       ROUND(SUM(
           CASE
               WHEN p.payment_method_id = 1
                    AND bt.payment_status IN ('due', 'partial')
                   THEN GREATEST(
                       bv.amount_due - COALESCE(p.amount_paid, 0),
                       0
                   )
               WHEN p.payment_method_id = 4
                    AND bt.payment_status = 'due'
                   THEN GREATEST(
                       bv.amount_due - COALESCE(p.amount_paid, 0),
                       0
                   )
               ELSE 0
           END
       ), 2) AS total_credit_sales,

       ROUND(SUM(
           CASE
               WHEN p.payment_method_id = 4
                    AND bt.payment_status IN ('paid', 'due')
                   THEN bv.amount_due
               ELSE 0
           END
       ), 2) AS total_cash_on_delivery_sales,

       ROUND(SUM(
           CASE
               WHEN p.payment_method_id = 2
                    AND bt.payment_status = 'paid'
                   THEN COALESCE(p.amount_paid, 0)
               ELSE 0
           END
       ), 2) AS total_card_sales

FROM basket_amount_due AS bv
JOIN basket AS bt
  ON bt.basket_id = bv.basket_id
LEFT JOIN (
    SELECT basket_id,
           payment_method_id,
           SUM(amount_paid) AS amount_paid
    FROM basket_payment
    GROUP BY basket_id, payment_method_id
) AS p
  ON p.basket_id = bv.basket_id
WHERE bv.sale_time >= CURDATE()
  AND bv.sale_time < CURDATE() + INTERVAL 1 DAY
GROUP BY DATE(bv.sale_time);

注意事项

这段查询有一个前提：每个 basket_id 只能对应一种付款方式。如果订单允许同时使用现金和银行卡拆分付款，就不能按付款方式直接重复归类 amount_due。此时需要在 basket_payment 中保存各付款方式分别承担的金额，再按该金额汇总。

这里的 total_credit_sales 表示“尚未收款的销售额”，并不是银行卡销售额。为了避免业务含义混淆，实际项目中可以将该列改名为：

total_outstanding_sales

金额字段应优先使用 DECIMAL，不要使用 FLOAT 或 DOUBLE，以免出现不必要的浮点精度误差。

备注：内容仅供参考。