SQL每日一题(20220801)
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;
DJOSIMON