sql - 嵌套自连接并创建多个总和

Question

我有一个基本的 parent /子女支出计划:

基础数据是一样的，所以我只是添加了一个类别列和 parent_id。这些有子记录:

我正在尝试汇总订单、相关订单和两者之间的差异的总计，如下所示:

按整体订单分组然后我也在寻找这样的东西:

无论哪种方式，我都可以毫无问题地获得 order_amount。这是一个简单的 JOIN 和 SUM。

由于我必须将发票支出加入到订单中，然后加入发票支出项目并将其相加，我被困在辅助 JOINS 上。

我正在寻找正确 JOIN 的方向，或者是否有更好的方法通过某种子查询等来解决这个问题。

Answer 1

按顺序总结，一种解决方案是使用条件聚合查询。一个技巧是检查 category 来决定是使用 expenditures.id 列的值还是 expenditures.parent_id 列的值作为分组标准:

SELECT
    CASE WHEN e.category = 'order' THEN e.id ELSE e.parent_id END expenditure_id,
    SUM(CASE WHEN e.category = 'order' THEN i.amount ELSE 0 END) order_amount,
    SUM(CASE WHEN e.category = 'invoice' THEN i.amount ELSE 0 END) order_amount,
    SUM(CASE WHEN e.category = 'order' THEN i.amount ELSE 0 END)
        - SUM(CASE WHEN e.category = 'invoice' THEN i.amount ELSE 0 END) balance
FROM expenditures e
LEFT JOIN expenditure_items i ON e.id = i.expenditure_id
GROUP BY CASE WHEN e.category = 'order' THEN e.id ELSE e.parent_id END
ORDER BY expenditure_id

Demo on DB Fiddle :

| expenditure_id | order_amount | order_amount | balance |
| -------------- | ------------ | ------------ | ------- |
| 1              | 3740         | 0            | 3740    |
| 2              | 11000        | 9350         | 1650    |

---

第二个查询，按项目代码汇总，基本上遵循相同的逻辑，但改为按同上代码分组:

SELECT
    i.code,
    SUM(CASE WHEN e.category = 'order' THEN i.amount ELSE 0 END) order_amount,
    SUM(CASE WHEN e.category = 'invoice' THEN i.amount ELSE 0 END) order_amount,
    SUM(CASE WHEN e.category = 'order' THEN i.amount ELSE 0 END)
        - SUM(CASE WHEN e.category = 'invoice' THEN i.amount ELSE 0 END) balance
FROM expenditures e
LEFT JOIN expenditure_items i ON e.id = i.expenditure_id
GROUP BY i.code
ORDER BY i.code;

Demo :

| code | order_amount | order_amount | balance |
| ---- | ------------ | ------------ | ------- |
| a    | 13400        | 8500         | 4900    |
| b    | 1340         | 850          | 490     |