leetcode刷题mysql 3.9

最新推荐文章于 2024-09-30 14:51:11 发布

陪小佳摸鱼

最新推荐文章于 2024-09-30 14:51:11 发布

阅读量295

点赞数 5

文章标签： leetcode mysql 算法

本文链接：https://blog.csdn.net/qq_55270691/article/details/136588673

版权

本文介绍了四个SQL查询问题，涉及用户访问间最大空档期计算、查找汇报CEO的员工、识别测验中游成绩学生以及任务状态连续时间段。每个查询使用了不同的聚合函数、窗口函数和连接操作来解决特定业务场景问题。

摘要由CSDN通过智能技术生成

1.假设今天的日期是 ‘2021-1-1’ 。
编写解决方案，对于每个 user_id ，求出每次访问及其下一个访问（若该次访问是最后一次，则为今天）之间最大的空档期天数 window 。
返回结果表，按用户编号 user_id 排序。
结果格式如下示例所示：
在这里插入图片描述

select t.user_id, max(TimeStampDiff(DAY, visit_date, next_day)) as biggest_window
from (SELECT user_id,
             visit_date,
             lead(visit_date, 1, '2021-1-1')
                  OVER (PARTITION BY user_id ORDER BY visit_date) AS next_day
      FROM UserVisits) as t
group by user_id

ps:lead 随便的那个日期加在最后，lag加在最前,TimeStampDiff(DAY, visit_date, next_day),算时间间隔，单位，小时间，大时间

2.编写解决方案，找出所有直接或间接向公司 CEO 汇报工作的职工的 employee_id 。
由于公司规模较小，经理之间的间接关系不超过 3 个经理。
可以以任何顺序返回无重复项的结果。
返回结果示例如下。
在这里插入图片描述

SELECT a.employee_id
FROM Employees a
LEFT JOIN Employees b ON a.manager_id = b.employee_id
LEFT JOIN Employees c ON b.manager_id = c.employee_id
WHERE c.manager_id = 1 AND a.employee_id != 1;

ps:画个图应该好理解
在这里插入图片描述
3.成绩处于中游的学生是指至少参加了一次测验, 且得分既不是最高分也不是最低分的学生。
编写解决方案，找出在所有测验中都处于中游的学生 (student_id, student_name)。不要返回从来没有参加过测验的学生。
返回结果表按照 student_id 排序。
返回结果格式如下。
在这里插入图片描述

SELECT r.student_id,s.student_name
FROM (
    SELECT 
        student_id,
        RANK() OVER (PARTITION BY exam_id ORDER BY score) AS up_asc,
        RANK() OVER (PARTITION BY exam_id ORDER BY score DESC) AS up_desc
    FROM exam
) r
left join Student s on s.student_id=r.student_id
GROUP BY student_id 
HAVING MIN(up_asc) != 1 AND MIN(up_desc) != 1 order by student_id

ps：left join+groupby+having顺序是这个，还有就是尽量不用with as，因为withas后正式的表没法接having

4.系统每天运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。
编写解决方案找出 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。
最后结果按照起始日期 start_date 排序
返回结果样例如下所示:
在这里插入图片描述

select type as period_state, min(date) as start_date, max(date) as end_date
from
(
    select type, date, subdate(date,row_number()over(partition by type order by date)) as diff
    from
    (
        select 'failed' as type, fail_date as date from Failed
        union all
        select 'succeeded' as type, success_date as date from Succeeded
    ) a
)a
where date between '2019-01-01' and '2019-12-31'
group by type,diff
order by start_date