sql - 从两个表中计算用户账户余额

Question

我有两张桌子：

1 付款表

PID  Amount  IsOk
=======================
10   50     true
10   92     false
14   73     true
14   22     true
15   10     true

2 取款表

PID  Amount  IsOk
=======================
10   23     true
14   98     false
14   15     true

我想要subtract(sum of withdraws amounts) and (sum of withdraws amounts) where IsOk=true这样的任何PID

PID  Balance  
=======================
10   27   //50-23
14   80   //73+22-15
15   10   //10

标签： sqlsql-servertsqlgroup-bysum

Answer 1

一种选择使用union all和聚合：

select pid, sum(amount) balance balance
from (
    select pid, amount from payment where isOk = 'true'
    union all
    seect pid, -amount from withdraws where isOk = 'true'
) t
group by pid