SQL跨表减法计算：按truck_id批量生成净额列表需求

需求说明：

• 涉及三个关联表：Expedition、ChainingExpedition、TruckMaintenanceExpense，共同关联字段为truck_id
• 计算逻辑：每辆卡车的收入净额 = 2020年以后两个收入表中去重的last_revenue总和 - 同期TruckMaintenanceExpense表中expense_type为'çeker'的去重price_dollar总和
• 需输出包含truck_id和difference（净额）的全量结果列表

修改后的SQL语句 #

WITH RevenueSummary AS (
    SELECT 
        truck_id,
        SUM(DISTINCT last_revenue) AS total_revenue
    FROM (
        SELECT truck_id, last_revenue 
        FROM Expedition 
        WHERE YEAR(departure_date) > 2020
        UNION ALL
        SELECT truck_id, last_revenue 
        FROM ChainingExpedition 
        WHERE YEAR(departure_date) > 2020
    ) combined_revenues
    GROUP BY truck_id
),
ExpenseSummary AS (
    SELECT 
        truck_id,
        SUM(DISTINCT price_dollar) AS total_expense
    FROM TruckMaintenanceExpense
    WHERE YEAR(payment_date) > 2020
      AND expense_type = 'çeker'
    GROUP BY truck_id
)
SELECT 
    COALESCE(r.truck_id, e.truck_id) AS truck_id,
    COALESCE(r.total_revenue, 0) - COALESCE(e.total_expense, 0) AS difference
FROM RevenueSummary r
FULL OUTER JOIN ExpenseSummary e ON r.truck_id = e.truck_id;

逻辑说明 #

1. RevenueSummary 公共表表达式：合并两个收入表2020年后的数据，按truck_id分组，对去重的last_revenue求和，得到每辆卡车的总收入
2. ExpenseSummary 公共表表达式：从维修费用表筛选2020年后、expense_type为'çeker'的数据，按truck_id分组，对去重的price_dollar求和，得到每辆卡车的总费用
3. 最终查询使用FULL OUTER JOIN关联两个汇总表，确保所有有收入或有费用的卡车都被纳入结果；用COALESCE处理NULL值，避免无收入/无费用时计算出错

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