连接SQL表，用sum将一列划分为3列

Question

表格">

id | dsi | amount | month  
2  | debt| -23.39 | 02  
2  | debt| -27.32 | 02  
2  | sav |  23.39 | 03  
2  | inv | 101.39 | 04  
2  | sav |  23.39 | 04  
2  | debt| -42.42 | 04  
2  | sav | 123.39 | 03  
2  | inv |  23.39 | 03  
2  | sav | 231.39 | 04  
2  | inv | 234.39 | 04  

我想按用户(id列)、月以及每月的债务、储蓄和投资总额查询该表。理想情况下，它看起来像这样:

month |  debt  |  sav  |  inv  |  
02    | -50.71 |       |       |  
03    |        | 146.78| 23.39 |  
04    | -42.42 | 231.39|234.39 | 

我已经尝试了很多不同的MySQL查询，包括内部连接，但我没有找到正确的方法。正确的查询方式是什么?

这是我最近的一次尝试:

cursor.execute("""SELECT a.month, debt, savings, investment   
FROM dsi a INNER JOIN dsi b   
ON a.month   
ON a.dsi=d.dsi    
GROUP BY a.month   
SUM(amount) AS debt WHERE (dsi = %s) AND (id = %s) GROUP BY month""", ("debt", id))
data = cursor.fetchall()

Answer 1

您只是想要条件聚合吗?

select month,
sum(case when dsi = 'debt' then amount end) as debt,
sum(case when dsi = 'sav' then amount end) as sav,
sum(case when dsi = 'inv' then amount end) as inv
from dsi
group by month