如何用SQL查询Payments与Expenses表中各分店的总利润？

表结构 #

Payments（收入表） #

• payment_id INT: 收入记录ID（主键）
• branch VARCHAR(50): 分店名称
• amount DECIMAL(10,2): 收入金额
• payment_date DATE: 收入日期

Expenses（支出表） #

• expense_id INT: 支出记录ID（主键）
• branch VARCHAR(50): 分店名称
• amount DECIMAL(10,2): 支出金额
• expense_date DATE: 支出日期

测试数据 #

Payments 表数据 #

INSERT INTO Payments (payment_id, branch, amount, payment_date) VALUES
(1, '北京朝阳店', 15000.00, '2024-01-05'),
(2, '北京朝阳店', 8000.00, '2024-01-12'),
(3, '上海浦东店', 22000.00, '2024-01-08'),
(4, '广州天河店', 10000.00, '2024-01-10');

Expenses 表数据 #

INSERT INTO Expenses (expense_id, branch, amount, expense_date) VALUES
(1, '北京朝阳店', 6000.00, '2024-01-06'),
(2, '上海浦东店', 9000.00, '2024-01-09'),
(3, '广州天河店', 4000.00, '2024-01-11'),
(4, '深圳南山店', 7000.00, '2024-01-13');

问题分析 #

直接用RIGHT OUTER JOIN关联两张表会触发两个问题：

1. 若某分店有多条收入/支出记录，关联后会产生笛卡尔积，导致金额被重复统计；
2. RIGHT JOIN仅能保留右表的所有分店，无法覆盖仅存在于左表的分店，导致统计不完整。

要实现全分店覆盖+准确统计，需先汇总单表数据，再基于全分店集合做关联。

正确SQL查询语句 #

WITH AllBranches AS (
    -- 获取所有出现过的分店（含仅收入/仅支出的分店）
    SELECT branch FROM Payments
    UNION
    SELECT branch FROM Expenses
),
PaymentSummary AS (
    -- 按分店汇总总收入
    SELECT branch, SUM(amount) AS total_payments
    FROM Payments
    GROUP BY branch
),
ExpenseSummary AS (
    -- 按分店汇总总支出
    SELECT branch, SUM(amount) AS total_expenses
    FROM Expenses
    GROUP BY branch
)
SELECT
    ab.branch,
    COALESCE(ps.total_payments, 0) AS total_payments,
    COALESCE(es.total_expenses, 0) AS total_expenses,
    COALESCE(ps.total_payments, 0) - COALESCE(es.total_expenses, 0) AS profit
FROM AllBranches ab
LEFT JOIN PaymentSummary ps ON ab.branch = ps.branch
LEFT JOIN ExpenseSummary es ON ab.branch = es.branch
ORDER BY ab.branch;

关键说明 #

• AllBranches CTE 整合两张表的分店，确保无遗漏；
• 先对单表做分组汇总，避免关联时的重复计算；
• 用COALESCE将NULL值转为0，保证无收入/支出的分店能显示0而非空值。

预期结果 #

内容的提问来源于stack exchange，提问作者John Alsace Mondoñedo