MySQL按支付方式和支付状态统计总销售额的SQL查询问题

SELECT 
    DATE(basket_stat.sale_time) AS stat_date,
    -- 总现金销售额：现金实收+货到付款实收
    ROUND(
        SUM(
            CASE 
                WHEN (m.payment_method_id = 1) OR (m.payment_method_id = 4 AND bt.payment_status = 'paid')
                THEN paid
                ELSE 0 
            END
        ), 2
    ) AS total_cash_sales,
    -- 总赊销额：所有未支付的金额=（应付-实付）汇总
    ROUND(
        SUM(
            CASE 
                WHEN m.payment_method_id IN (1,4)
                THEN (amount_due - paid)
                ELSE 0 
            END
        ), 2
    ) AS total_credit_sales,
    -- 总货到付款销售额：货到付款所有订单应付金额汇总
    ROUND(
        SUM(
            CASE WHEN m.payment_method_id = 4 THEN amount_due ELSE 0 END
        ), 2
    ) AS total_cod_sales,
    -- 总刷卡销售额：刷卡支付的实收金额
    ROUND(
        SUM(
            CASE WHEN m.payment_method_id = 2 AND bt.payment_status = 'paid' THEN paid ELSE 0 END
        ), 2
    ) AS total_card_sales
FROM (
    -- 先拿到每个篮子的基础统计数据，和你最开始获取明细的逻辑保持一致
    SELECT 
        bv.basket_id,
        bv.sale_time,
        bv.amount_due,
        ROUND(SUM(p.amount_paid), 2) as paid,
        bt.payment_status,
        bt.payment_method_id
    FROM basket_amount_due bv 
    JOIN basket bt USING(basket_id)
    LEFT JOIN basket_payment p USING(basket_id)
    GROUP BY bv.basket_id, bv.sale_time, bv.amount_due, bt.payment_status, bt.payment_method_id
) AS basket_stat
JOIN payment_method m USING(payment_method_id)
-- 要查所有日期就注释掉下面这行
WHERE DATE(basket_stat.sale_time) = CURDATE()
GROUP BY stat_date;

逻辑说明 #

• 先通过子查询生成每个订单的应付、实付、支付状态、支付方式ID基础数据，和你最开始获取明细的逻辑保持一致，避免多表关联时聚合逻辑出错
• 所有统计用条件聚合实现，将CASE判断放在SUM函数内部，匹配你给出的统计规则：
  • 总现金销售额：取现金支付的所有实收金额 + 货到付款已完成订单的实收金额
  • 总赊销额：取现金、货到付款两类支付方式的所有未支付金额（应付减实付）汇总
  • 总货到付款销售额：取所有货到付款订单的应付金额总和
  • 总刷卡销售额：取刷卡支付已完成订单的实收金额

示例数据计算结果 #

用你给出的6条测试数据执行该SQL，返回结果如下：

和你给出的手动计算结果完全一致。

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