SQL练习71：牛客每个人最近的登录日期(六)

最新推荐文章于 2022-06-26 16:55:39 发布

原创最新推荐文章于 2022-06-26 16:55:39 发布 · 2.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #数据库

Mysql数据库实战题（每日打卡）专栏收录该内容

67 篇文章

订阅专栏

本文介绍如何使用SQL查询统计用户在截止日期的累计通过题数，包括未刷题用户，通过0题情况，以及按日期和姓名排序的结果。关键步骤涉及窗口函数和左连接操作。

SQL练习71：牛客每个人最近的登录日期6

题目链接：牛客网

题目描述
牛客每天有很多人登录，请你统计一下牛客每个用户查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题。不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0。
有一个登录(login)记录表，简况如下:
在这里插入图片描述
第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网。
…
第5行表示id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网。

有一个刷题（passing_number)表，简况如下:
在这里插入图片描述
第1行表示id为2的用户在2020-10-12通过了4个题目。
…
第3行表示id为1的用户在2020-10-13提交了代码但是没有通过任何题目。
第4行表示id为4的用户在2020-10-13通过了2个题目

还有一个用户(user)表，简况如下:
在这里插入图片描述
请你写出一个sql语句查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出，上面的例子查询结果如下:
在这里插入图片描述
查询结果表明:
fh在2020-10-12为止，总共通过了4道题，输出为4。
wangchao在2020-10-12为止，总共通过了1道题，总计为1。
tm在2020-10-12为止只登陆了没有刷题，故没有显示出来。
tm在2020-10-13为止刷了题，但是却没有通过任何题目，总计为0。
wangchao在2020-10-13通过2道，但是加上前面2020-10-12通过1道，故在2020-10-13为止总共通过了3道题，总计为3。

解法
根据题目的要求，并不需要使用到login表，可以采用sum函数进行开窗处理，按user_id进行分区，再通过date升序排序，进而实现了在每个user_id分区中以升序日期排序的通过题数的逐个递加。之后使用左外连接将结果与user表连接。

SELECT name AS u_n, date, SUM(number) OVER(PARTITION BY user_id ORDER BY date) AS ps_num
FROM passing_number p
LEFT JOIN USER u
ON p.user_id = u.id
ORDER BY date, u_n