2021-06-06

#不同性别每日分数总计

写一条SQL语句查询每种性别在每一天的总分，并按性别和日期对查询结果排序

展示效果：

+--------+------------+-------+
| gender | day        | total |
+--------+------------+-------+
| F      | 2019-12-30 | 17    |
| F      | 2019-12-31 | 40    |
| F      | 2020-01-01 | 57    |
| F      | 2020-01-07 | 80    |
| M      | 2019-12-18 | 2     |
| M      | 2019-12-25 | 13    |
| M      | 2019-12-30 | 26    |
| M      | 2019-12-31 | 29    |
| M      | 2020-01-07 | 36    |
+--------+------------+-------+

建表语句：

Create table If Not Exists 72_Scores (player_name varchar(20), gender varchar(1), day date, score_points int);
Truncate table 72_Scores;
insert into 72_Scores (player_name, gender, day, score_points) values ('Aron', 'F', '2020-01-01', '17');
insert into 72_Scores (player_name, gender, day, score_points) values ('Alice', 'F', '2020-01-07', '23');
insert into 72_Scores (player_name, gender, day, score_points) values ('Bajrang', 'M', '2020-01-07', '7');
insert into 72_Scores (player_name, gender, day, score_points) values ('Khali', 'M', '2019-12-25', '11');
insert into 72_Scores (player_name, gender, day, score_points) values ('Slaman', 'M', '2019-12-30', '13');
insert into 72_Scores (player_name, gender, day, score_points) values ('Joe', 'M', '2019-12-31', '3');
insert into 72_Scores (player_name, gender, day, score_points) values ('Jose', 'M', '2019-12-18', '2');
insert into 72_Scores (player_name, gender, day, score_points) values ('Priya', 'F', '2019-12-31', '23');
insert into 72_Scores (player_name, gender, day, score_points) values ('Priyanka', 'F', '2019-12-30', '17');

最终SQL：

select
	gender,
	`day`,
	sum(score_points)over(partition by gender order by day) total
from
	user