SQL每日一题（20220801）

参考：https://mp.weixin.qq.com/s?__biz=MzA3MTg4NjY4Mw==&mid=2457328831&idx=3&sn=e1a2e9a92a17b44ff463be4e59f9a760&chksm=88a5ce8bbfd2479dd330ece19cbd12751cba8ab3733cc10634ed54dfbdd4ba9d4f4735820b86&scene=178&cur_album_id=1790847024611852294#rd

220629

题目

原始数据如下：

日期	类型	金额
2021-01-01	借款	100
2021-01-31	借款	-50
2021-01-20	还款	50
2021-01-23	还款	-20
2021-02-01	借款	100
2021-02-28	借款	-50
2021-02-20	还款	50
2021-02-23	还款	-20
2021-03-01	借款	100
2021-03-31	借款	-50
2021-03-20	还款	50
2021-03-23	还款	-20

希望得到：

月份	借款金额	还款金额	累计借款	累计还款
2021-01	50	30	50	30
2021-02	50	30	100	60
2021-03	50	30	150	90

数据脚本

CREATE TABLE F0801
(
    日期 DATE,
    类型 VARCHAR(10),
    金额 INT
);

INSERT INTO F0801 VALUES ('2021-01-01','借款',100);
INSERT INTO F0801 VALUES ('2021-01-31','借款',-50);
INSERT INTO F0801 VALUES ('2021-01-20','还款',50);
INSERT INTO F0801 VALUES ('2021-01-23','还款',-20);
INSERT INTO F0801 VALUES ('2021-02-01','借款',100);
INSERT INTO F0801 VALUES ('2021-02-28','借款',-50);
INSERT INTO F0801 VALUES ('2021-02-20','还款',50);
INSERT INTO F0801 VALUES ('2021-02-23','还款',-20);
INSERT INTO F0801 VALUES ('2021-03-01','借款',100);
INSERT INTO F0801 VALUES ('2021-03-31','借款',-50);
INSERT INTO F0801 VALUES ('2021-03-20','还款',50);
INSERT INTO F0801 VALUES ('2021-03-23','还款',-20);

我的答案

select left(`日期`, 7)                  as '月份',
       SUM(IF(`类型` = '借款', `金额`, '')) as '借款金额',
       SUM(IF(`类型` = '还款', `金额`, '')) as '还款金额',
       ''                             as '累计借款',
       ''                             as '累计还款'
from f0801
group by left(`日期`, 7);

参考答案

select a.*
     , sum(`借款金额`) over (order by `月份`) as '累计借款'
     , sum(`还款金额`) over (order by `月份`) as '累计还款'
from (
         select left(`日期`, 7)                  as '月份',
                SUM(IF(`类型` = '借款', `金额`, '')) as '借款金额',
                SUM(IF(`类型` = '还款', `金额`, '')) as '还款金额'
         from f0801
         group by left(`日期`, 7)
     ) a;