SQL每日一题(20210402)借还款金额=当月金额之和

SQL每日一题(20210714)
SQL每日一题(20211011)
SQL每日一题(20220125)
SQL每日一题(20220801)

with tmp as (
select     DATE_FORMAT(t.日期,'%Y-%m') dt, 
              DATE_FORMAT(t.日期,'%Y')dt_y, 
              sum(case when t.类型 = '借款' then t.金额 end)  借款金额, 
              sum(case when t.类型 = '还款' then t.金额 end)  还款金额
from T0402 t
group by DATE_FORMAT(t.日期,'%Y-%m'), 
               DATE_FORMAT(t.日期,'%Y'))

select     t.dt 月份, 
              t.借款金额, 
              t.还款金额, 
              sum(t.借款金额) over(partition by t.dt_y order by t.dt ) 累计借款,    
              sum(t.还款金额) over(partition by t.dt_y order by t.dt ) 累计还款
from tmp t

select 月份,
       jk AS 借款金额,
       hk AS 还款金额,
       SUM(jk) OVER(ORDER BY 月份) AS 累计借款,
       SUM(hk) OVER(ORDER BY 月份) AS 累计还款
  from (SELECT to_char(日期, 'yyyy-mm') AS 月份, 类型, 金额 FROM T0402 t) pivot(SUM(金额) for 类型 IN ('借款' jk, '还款' hk));