MySQL按receipt_id计算日/月/年未结金额：paid_amount仅计一次

表结构 #

SELECT 
    sale_id,
    unit_id,
    quantity_sold,
    total_price,
    bought_price,
    paid_amount,
    tax_percentage,
    sold_by,
    receipt_id,
    customer_id,
    shop_id,
    payment_method,
    sale_date
FROM sales;

需求说明 #

需要统计日、月、年三个周期的未结金额，计算规则：

• 单条销售记录的未结金额逻辑为 total_price - paid_amount
• 若多条记录共享同一receipt_id，对应的paid_amount仅需扣除一次，禁止重复扣除

错误尝试的查询 #

之前尝试用子查询关联receipt_id去重，但结果不正确，查询语句如下：

SELECT
        COALESCE(FORMAT(SUM(CASE 
                WHEN DATE(s.sale_date) = CURDATE() THEN s.total_price 
                ELSE 0 
            END), 2), '0.00') AS todays_income,
        COALESCE(FORMAT(SUM(CASE 
                WHEN YEAR(s.sale_date) = ? AND MONTH(s.sale_date) = MONTH(CURDATE()) THEN s.total_price 
                ELSE 0 
            END), 2), '0.00') AS monthly_income,
        COALESCE(FORMAT(SUM(CASE 
                WHEN YEAR(s.sale_date) = ? THEN s.total_price 
                ELSE 0 
            END), 2), '0.00') AS yearly_income,

        -- Calculate profit correctly (bought_price * quantity_sold)
        COALESCE(FORMAT(SUM(CASE 
                WHEN DATE(s.sale_date) = CURDATE() THEN (s.total_price - (s.bought_price * s.quantity_sold)) 
                ELSE 0 
            END), 2), '0.00') AS todays_profit,
        COALESCE(FORMAT(SUM(CASE 
                WHEN YEAR(s.sale_date) = ? AND MONTH(s.sale_date) = MONTH(CURDATE()) THEN (s.total_price - (s.bought_price * s.quantity_sold)) 
                ELSE 0 
            END), 2), '0.00') AS monthly_profit,
        COALESCE(FORMAT(SUM(CASE 
                WHEN YEAR(s.sale_date) = ? THEN (s.total_price - (s.bought_price * s.quantity_sold)) 
                ELSE 0 
            END), 2), '0.00') AS yearly_profit,

        -- Calculate outstanding amount (total_price - paid_amount) correctly by considering distinct receipt_id
        COALESCE(FORMAT(SUM(CASE 
                WHEN DATE(s.sale_date) = CURDATE() THEN (s.total_price - 
                (SELECT COALESCE(SUM(paid_amount), 0) 
                 FROM sales WHERE receipt_id = s.receipt_id GROUP BY receipt_id))
                ELSE 0 
            END), 2), '0.00') AS todays_outstanding,
        COALESCE(FORMAT(SUM(CASE 
                WHEN YEAR(s.sale_date) = ? AND MONTH(s.sale_date) = MONTH(CURDATE()) THEN (s.total_price - 
                (SELECT COALESCE(SUM(paid_amount), 0) 
                 FROM sales WHERE receipt_id = s.receipt_id GROUP BY receipt_id))
                ELSE 0 
            END), 2), '0.00') AS monthly_outstanding,
        COALESCE(FORMAT(SUM(CASE 
                WHEN YEAR(s.sale_date) = ? THEN (s.total_price - 
                (SELECT COALESCE(SUM(paid_amount), 0) 
                 FROM sales WHERE receipt_id = s.receipt_id GROUP BY receipt_id))
                ELSE 0 
            END), 2), '0.00') AS yearly_outstanding

        FROM sales s

正确解决方案 #

核心思路是先对receipt_id进行聚合，计算每个收据对应的总应收（SUM(total_price)）和实付金额（同一receipt_id的paid_amount只取一次），再基于聚合后的结果按时间周期统计未结金额。

完整查询语句：

WITH receipt_summary AS (
    SELECT
        receipt_id,
        SUM(total_price) AS receipt_total,
        MAX(paid_amount) AS receipt_paid, -- 同一receipt_id的paid_amount只取一次，若业务允许多次付款可改为SUM(paid_amount)
        MIN(sale_date) AS receipt_date -- 用收据下最早的销售日期作为周期判断依据，可根据业务调整为最晚日期
    FROM sales
    GROUP BY receipt_id
)
SELECT
    -- 原有收入统计逻辑
    COALESCE(FORMAT(SUM(CASE WHEN DATE(s.sale_date) = CURDATE() THEN s.total_price ELSE 0 END), 2), '0.00') AS todays_income,
    COALESCE(FORMAT(SUM(CASE WHEN YEAR(s.sale_date) = YEAR(CURDATE()) AND MONTH(s.sale_date) = MONTH(CURDATE()) THEN s.total_price ELSE 0 END), 2), '0.00') AS monthly_income,
    COALESCE(FORMAT(SUM(CASE WHEN YEAR(s.sale_date) = YEAR(CURDATE()) THEN s.total_price ELSE 0 END), 2), '0.00') AS yearly_income,

    -- 原有利润统计逻辑
    COALESCE(FORMAT(SUM(CASE WHEN DATE(s.sale_date) = CURDATE() THEN (s.total_price - (s.bought_price * s.quantity_sold)) ELSE 0 END), 2), '0.00') AS todays_profit,
    COALESCE(FORMAT(SUM(CASE WHEN YEAR(s.sale_date) = YEAR(CURDATE()) AND MONTH(s.sale_date) = MONTH(CURDATE()) THEN (s.total_price - (s.bought_price * s.quantity_sold)) ELSE 0 END), 2), '0.00') AS monthly_profit,
    COALESCE(FORMAT(SUM(CASE WHEN YEAR(s.sale_date) = YEAR(CURDATE()) THEN (s.total_price - (s.bought_price * s.quantity_sold)) ELSE 0 END), 2), '0.00') AS yearly_profit,

    -- 修正后的未结金额统计
    COALESCE(FORMAT(SUM(CASE WHEN DATE(rs.receipt_date) = CURDATE() THEN (rs.receipt_total - rs.receipt_paid) ELSE 0 END), 2), '0.00') AS todays_outstanding,
    COALESCE(FORMAT(SUM(CASE WHEN YEAR(rs.receipt_date) = YEAR(CURDATE()) AND MONTH(rs.receipt_date) = MONTH(CURDATE()) THEN (rs.receipt_total - rs.receipt_paid) ELSE 0 END), 2), '0.00') AS monthly_outstanding,
    COALESCE(FORMAT(SUM(CASE WHEN YEAR(rs.receipt_date) = YEAR(CURDATE()) THEN (rs.receipt_total - rs.receipt_paid) ELSE 0 END), 2), '0.00') AS yearly_outstanding
FROM sales s
JOIN receipt_summary rs ON s.receipt_id = rs.receipt_id;

关键说明 #

1. 通过CTEreceipt_summary按receipt_id聚合，确保每个收据的paid_amount仅计算一次，避免重复扣除
2. 保留原有收入、利润的统计逻辑，同时基于聚合后的收据数据计算未结金额
3. 若业务中同一receipt_id存在多次付款记录，可将MAX(paid_amount)改为SUM(paid_amount)以累计实付金额
4. 测试数据下，期望未结金额结果为15,933

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