|
马上注册,结交更多好友,享用更多功能^_^
您需要 登录 才可以下载或查看,没有账号?立即注册
x
本帖最后由 糖逗 于 2020-7-2 16:21 编辑
题目描述:
- 写一条SQL语句查询每种性别在每一天的总分,并按性别和日期对查询结果排序
- 下面是查询结果格式的例子:
- Scores表:
- +-------------+--------+------------+--------------+
- | player_name | gender | day | score_points |
- +-------------+--------+------------+--------------+
- | Aron | F | 2020-01-01 | 17 |
- | Alice | F | 2020-01-07 | 23 |
- | Bajrang | M | 2020-01-07 | 7 |
- | Khali | M | 2019-12-25 | 11 |
- | Slaman | M | 2019-12-30 | 13 |
- | Joe | M | 2019-12-31 | 3 |
- | Jose | M | 2019-12-18 | 2 |
- | Priya | F | 2019-12-31 | 23 |
- | Priyanka | F | 2019-12-30 | 17 |
- +-------------+--------+------------+--------------+
- 结果表:
- +--------+------------+-------+
- | 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 |
- +--------+------------+-------+
- 来源:力扣(LeetCode)
- 链接:https://leetcode-cn.com/problems/running-total-for-different-genders
- 著作权归领扣网络所有。商业转载请联系官方授权,非商业转载请注明出处。
复制代码
- select s1.gender,s1.day,sum(s2.score_points) as total
- from scores as s1 join scores as s2
- where s1.gender=s2.gender and s1.day>=s2.day
- group by s1.gender,s1.day
复制代码
其他参考:
1.窗口函数,但MYSQL实现不了。
https://blog.csdn.net/qq_41805514/article/details/81772182 |
|