1077 项目员工 III
# Write your MySQL query statement below
select project_id, employee_id
from (
select project_id, e.employee_id, rank() over(partition by project_id order by experience_years desc) as rk
from Employee e
join Project p
on e.employee_id = p.employee_id
) tmp
where tmp.rk = 1;
1285 找到连续区间的开始和结束数字
# Write your MySQL query statement below
select min(log_id) as start_id, max(log_id) as end_id
from (
select log_id, log_id - rank() over(order by log_id) as num
from Logs
) t
group by num
思路
- 先给每个数进行排名
- 用这些数减去自己的排名，如果减了之后的结果是一样的，说明这几个数是连续的
- 用logid减去排名得出来的数进行group by，也就是把连续的数全都放在一个一个小组里面，求出每个小组的最大值和最小值就可以了
1596 每位顾客最经常订购的商品
- 1596题
select t.customer_id, t.product_id, p.product_name
from (
select customer_id, product_id,
rank() over(partition by customer_id order by count(product_id) desc) as rk
from Orders
group by customer_id, product_id
) t
join Products p
on t.product_id = p.product_id
where rk = 1
总结
- 这里有个坑，count并不会根据partition来分组，所以必须根据group by 来分组计算出count的数量；其次group by在窗口函数之前执行。
1709 访问日期之间最大的空档期
select user_id, max(datediff(next_visit, visit_date)) as biggest_window
from (
select user_id,
visit_date,
lead(visit_date, 1, "2021-01-01") over(partition by user_id
order by visit_date) as next_visit
from UserVisits
) tmp
group by user_id
order by user_id;
总结
-
注意lead(x,y,z) over()的参数，x代表要寻找的列，y代表往后走几个位置，z代表当x+y找不到记录时的默认值。
此题中lead(visit_date,1,‘2021-01-01’)就说明要寻找的是visit_date列，1代表visit_date往后走1条记录，‘2021-01-01’代表往后走找不到记录时，默认为’2021-01-01’。这里的前后是指用order by排序过后的前后。
1270 向公司CEO汇报工作的所有人
文章来源：https://www.toymoban.com/news/detail-432377.html
# 写法1
select employee_id from Employees
where manager_id in(
select employee_id from Employees
where manager_id in
(select employee_id from Employees where manager_id=1)
) and employee_id <> 1;
# 写法2
select distinct e1.employee_id
from Employees e1,Employees e2, Employees e3
where e1.manager_id=e2.employee_id and e2.manager_id=e3.employee_id
and e3.manager_id=1 and e1.employee_id !=1
总结
- 思路1：嵌套查询
- 思路2：三表联合，该方法只有在老板的
employee_id=manager_id时才生效。 - 对于思路2，一开始疑惑的点为什么不是四表查询，一开始认为
employee_id=7这个员工无法查询得到，但如果画出表来我们可以发现。表三中employee_id=2,manager_id=1的记录可以被查询出来，如下：
["employee_id", "employee_name", "manager_id", #表1
"employee_id", "employee_name", "manager_id", #表2
"employee_id", "employee_name", "manager_id"] #表3
[7, "Luis", 4,
4, "Daniel", 2,
2, "Bob", 1]
1412 查找成绩处于中游的学生
- 1412题
# 写法1
select distinct e.student_id, student_name
from Exam e
left join Student s
on e.student_id = s.student_id
where e.student_id not in (
select student_id from Exam
where (exam_id, score) in ((select exam_id, max(score) from Exam group by exam_id)
union all (select exam_id, min(score) from Exam group by exam_id))
)
order by student_id;
# 写法2
# 根据成绩升序和降序排序，根据学生id分组，去掉成绩排名中为1的记录
select e.student_id, student_name
from (
select student_id,
rank() over(partition by exam_id order by score desc) max_score_rk,
rank() over(partition by exam_id order by score) min_score_rk
from Exam
) e
left join Student s
on e.student_id = s.student_id
group by e.student_id
having min(e.max_score_rk) <> 1 and min(e.min_score_rk) <> 1
order by e.student_id;
1767 寻找没有被执行的任务对
- 1767题
with recursive t as
(
select task_id, subtasks_count subtask_id from Tasks
union all
select task_id, subtask_id-1 from t where subtask_id > 1
)
select t.task_id, t.subtask_id
from t
left join Executed e
on t.task_id = e.task_id and t.subtask_id = e.subtask_id
where e.subtask_id is null;
总结
通过recursive直接生成每个任务对应的所有可能的（主任务id， 子任务id）组合，下面是recursive的用法。文章来源地址https://www.toymoban.com/news/detail-432377.html
WITH RECURSIVE cte (n) AS
( select 初始值 from table
union all
select 递归内容 from cte where (终止条件)
)
参考
- 1285题思路
- 行转列7种方法
到了这里，关于【SQL篇】窗口函数和公共表达式的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
