php - MySQL查询贷方和借方从同一表

我有一个MySql表来控制借方和贷方。看起来像这样

id | status | type  | amount | value | expiration | parent_id | company_id |
---|--------|------ |--------|-------|------------|-----------|------------|
1  |    1   |credit | 46     | 42.00 | 2018-04-01 | 0         | 1          |
2  |    1   |credit | 33     | 194.00| 2017-07-07 | 0         | 1          |
3  |    1   |credit | 49     | 17.00 | 2016-11-11 | 0         | 1          |
4  |    1   |debit  | 1      | NULL  | NULL       | 1         | 1          |
5  |    1   |debit  | 1      | NULL  | NULL       | 1         | 1          |
6  |    1   |debit  | 1      | NULL  | NULL       | 2         | 1          |
7  |    1   |debit  | 1      | NULL  | NULL       | 2         | 1          |
8  |    1   |debit  | 1      | NULL  | NULL       | 2         | 1          |
9  |    1   |debit  | 1      | NULL  | NULL       | 2         | 1          |
10 |    1   |debit  | 1      | NULL  | NULL       | 3         | 1          |

我想知道一个人现在有多少学分。
对于状态为1且到期日>立即（）的每个父贷方，对状态为1且到期日>立即（）的所有借方进行求和
然后从它的父信用中减去
在这种情况下我应该得到这样的东西
总学分=（46-2）+（33-4）
到目前为止我得到的是

SELECT SUM(amount) as tot_debit
FROM credits
WHERE company_id = 1
      AND status = 1
      AND type = 'debit'
      AND parent_id IN (SELECT id
                         FROM credits
                         WHERE company_id = 1
                           AND status = 1
                           AND expiration > NOW()
                           AND type = 'credit')
GROUP BY parent_id

有什么办法让这工作吗？
谢谢

最佳答案

您可以使用相关性获得预期结果：

SELECT SUM(amount)
       -
       COALESCE((SELECT SUM(amount)
                 FROM credits AS c2
                 WHERE company_id = 1 AND status = 1 AND
                       type = 'debit' AND
                       c2.parent_id = c1.id), 0)
FROM credits AS c1
WHERE company_id = 1 AND status = 1 AND
      type = 'credit' AND expiration > NOW();

Demo here