mysql - 如何在mySQL中按数据分组并一次计算？

Question

======================================| InputType | Payable | Amount | pID |======================================| Expenses  | 10,000  | 5,000  |  1  |--------------------------------------| Expenses  | 10,000  | 5,000  |  1  |--------------------------------------| Expenses  | 10,000  | 5,000  |  2  |--------------------------------------| Deposit   | 5,000   | 4,000  |  1  |--------------------------------------| Deposit   | 15,000  | 10,000 |  2  |--------------------------------------

如果我想从上表中这样查看余额:

==============================| pID | Balance1  | Balance2 |==============================| 1   |  24,000  |   15,000  |------------------------------| 2   |  20,000  |   20,000  |------------------------------

pID 1 的计算:

应付(费用)10,000+10,000 + 金额(存款)4,000 = 余额 1 24,000

应付(费用)5,000+5,000 + 金额(存款)5,000 = 余额2 15,000

pID 2 的计算:

应付(费用)10,000 + 金额(存款)10,000 = 余额 1 20,000

应付(费用)5,000 + 金额(存款)15,000 = 余额 2 20,000

• 获取数据后如何进行计算？

Answer 1

如果 InputType 只能有两个不同的状态，我想你正在寻找这样的东西:

select
  pID,
  sum(case when InputType='Expenses' then Payable else Amount end) as Balance1,
  sum(case when InputType='Expenses' then Amount else Payable end) as Balance2
from tbl
group by pID