时间戳

首页 > 解决方案 > 查询父表时如何获取子表列的总和？

mysql - 查询父表时如何获取子表列的总和？

问题描述

我有两张桌子

账户表（账户）

id      name
1       Account 1
2       Account 2
3       Account 3
4       Account 2

事务表（事务）

id      account_id     type      amount     datetime
1       1              credit    500        2020-04-01 06:00:00
2       1              credit    300        2020-04-01 06:00:00
3       2              credit    100        2020-04-01 06:00:00
4       2              debit     50         2020-04-01 06:00:00
5       1              debit     600        2020-04-01 06:00:00
6       3              credit    1000       2020-04-01 06:00:00
7       1              credit    100

id, name, balance我的目标是在一次查询中获得帐户。余额将根据SUM of Credit Amount - SUM of Debit Amount给定帐户的交易表计算。

目标输出

   id      name          balance
   1       Account 1     300
   2       Account 2     50
   3       Account 3     1000
   4       Account 4     0

可能的查询

SELECT id, name, ((SELECT SUM(amount) FROM transaction WHERE type = 'credit' AND account_id = {ACCOUNT_ID} ) - (SELECT SUM(amount) FROM transaction WHERE type = 'debit' AND account_id = {ACCOUNT_ID} )) as balance

是否可以在一个查询中执行此操作，如果可以，如何执行。

标签： mysqlsqlselect

解决方案

您可以在派生表中进行聚合，以避免必须按顶级字段进行分组的问题。例如：

SELECT a.id, a.name, COALESCE(b.balance, 0) AS balance
FROM account a
LEFT JOIN (
  SELECT account_id,
         SUM(CASE WHEN type='credit' THEN amount ELSE 0 END) -
         SUM(CASE WHEN type='debit' THEN amount ELSE 0 END) AS balance
  FROM transaction
  GROUP BY account_id
) b ON b.account_id = a.id

输出：

id  name        balance
1   Account 1   300
2   Account 2   50
3   Account 3   1000
4   Account 2   0

SQLFiddle 上的演示

推荐阅读