sql - PostgreSQL加入多个表

Question

我有三张桌子user，deposit和withdraw。

user：

 | id | name    |
 | -: | :------ |
 |  1 | Lorem   |
 |  2 | Ipsum   |
 |  3 | Dolores |

deposit：

 user_id | amount
 ------: | -----:
       1 |   1000
       3 |   3000

withdraw：

 user_id | amount
 ------: | -----:
       1 |   2000
       2 |   3000

我的问题是如何加入表格，预期结果：

 id |  sum_deposit |  sum_withdraw
 -: | -----------: | ------------:
  1 |         1000 |          2000
  2 |         null |          3000
  3 |         3000 |          null

标签： sqlpostgresql

Answer 1

演示：db<>小提琴

首先SUM()是deposits和withdraws数据，然后是LEFT JOIN：

SELECT
    *
FROM users u

LEFT JOIN (
   SELECT 
       user_id,
       SUM(amount)
   FROM deposit
   GROUP BY user_id
) d ON u.id = d.user_id

LEFT JOIN (
   SELECT 
       user_id,
       SUM(amount)
   FROM withdraw
   GROUP BY user_id
) w ON u.id = w.user_id