【SQL高频练习带刷】day12：窗口函数和公共表表达式（CTE）

爱写代码的July

于 2024-08-31 02:45:00 发布

阅读量1.9k

点赞数 14

分类专栏： SQL习题带刷文章标签： sql 数据库算法 mysql leetcode

本文链接：https://blog.csdn.net/Liu_y_xin/article/details/141727824

版权

SQL习题带刷专栏收录该内容

44 篇文章 5 订阅

订阅专栏

题目一：项目员工III

题目要求：

编写解决方案，报告在每一个项目中 经验最丰富 的雇员是谁。如果出现经验年数相同的情况，请报告所有具有最大经验年数的员工。

返回结果表 无顺序要求。

1077. 项目员工 III - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

本节习题均可使用窗口函数求解，由于MySQL老一些的版本不支持窗口函数，所以我尽量提供窗口函数和不使用窗口函数的两种方法。

本题属于排名问题，使用窗口函数的话非常简单，只需要按照工程id进行分组排序，就可以找到排名第一的员工了。

如果不使用窗口函数的话，可以使用两个子查询实现。我们首先建立一张包含所有信息的临时表，我们直接左连接两张表就可以获得，我们以这张临时表作为基础进行后续的查询。接着我们需要找到经验最丰富的员工的工作时长，也就是按照项目id进行分组，统计最大的经验年数，但是仅仅靠这个查询，我们无法找到所有的该工作年限的员工信息，因此我们需要在刚刚的临时表中作为主查询，查找临时表中项目id和经验年限与子查询中查到的项目id和最大经验年限相等的数据。

运行代码示例：

select project_id , employee_id from(
select project_id , t1.employee_id,
rank() over (partition by t1.project_id order by t2.experience_years desc) rn
from Project t1
left join Employee t2
on t1.employee_id = t2.employee_id 
) t
where rn=1

select project_id,employee_id from (
    select e.employee_id,name,experience_years as year,project_id
    from Employee e
    left join Project p
    using(employee_id)
) t
where (project_id,year) in (select project_id,max(experience_years) as year
    from Employee e
    left join Project p
    using(employee_id)
    group by project_id
)

题目二：找到连续区间的开始和结束数字

题目要求：

编写解决方案，得到 Logs 表中的连续区间的开始数字和结束数字。

返回结果表按照 start_id 排序。

1285. 找到连续区间的开始和结束数字 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

本题也是非常经典的一道窗口函数的题目，也就是连续区间问题，对于这种连续区间的问题，我们可以使用字段-行号的方法，如果区间连续递增，那么它们减去对应的行号所得到的结果一定相等，我们按照结果进行分组，就能找到每一组连续的区间了。对于本题而言，我们找到了每一个连续区间，再在该区间内找到最大值和最小值即可。

窗口函数是这种题型的通用解法（甚至可以说是唯一解法了，很少看到有人用其他解法的）。从力扣题解里抄了一个大佬的表连接法。如果一个区间连续，那么我们要寻找的开始和结束节点的前一个数字和后一个数字肯定不在表中。也就是我们查询出

一个连续数字的区间， start_id - 1 和 end_id + 1 肯定不在表中 log_id - 1不在表中的log_id，就是开始数字，同理，我们可以找到所有的结束数字。问题在于如何将这两组数据连接起来呢？一个区间有 start_id ，就一定会有 end_id ，所以 start_id 和 end_id 的数量应该是相同的，所以可以使用排序函数分别对 l1 和 l2 表进行排序，使用他们所在的位次作为连接依据（好像仍然没绕过窗口函数.....）。另一种相似的思路可以参考解法三，前面都一样，对最后连接的条件不一样。

运行代码示例：

SELECT MIN(log_id) START_ID,MAX(log_id) END_ID
FROM (
    SELECT DISTINCT log_id, log_id - ROW_NUMBER() OVER ( ORDER BY log_id ASC ) reference
    FROM Logs
) T
GROUP BY reference
ORDER BY START_ID

with l1 as (
	select
		log_id start_id,
		row_number() over(order by log_id) as rank_id
	from logs where log_id - 1 not in (select log_id from logs)
),
l2 as (
  select
	  log_id end_id,
	  row_number() over(order by log_id) as rank_id
  from logs where log_id + 1 not in (select log_id from logs)
)
select start_id, end_id from l1 join l2 using(rank_id);

select head.log_id start_id, min(tail.log_id) end_id from
(select log_id from Logs where (log_id-1) not in (select log_id from Logs)) head,
(select log_id from Logs where (log_id+1) not in (select log_id from Logs)) tail
where head.log_id <= tail.log_id
group by head.log_id

题目三：每位顾客最经常订购的商品

题目要求：

写一个解决方案，找到每一个顾客最经常订购的商品。

结果表单应该有每一位至少下过一次单的顾客 customer_id , 他最经常订购的商品的 product_id 和 product_name。

返回结果 没有顺序要求。

1596. 每位顾客最经常订购的商品 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

首先计算出每个客户对应的最大的产品数量和 customer_id 和对应的产品id、产品数量、产品名称。我们将这两张表进行连接，查出产品数量=最大产品数量的产品即可。

运行代码示例：

select a1.customer_id, a2.product_id, a2.product_name
from (
    select customer_id, max(num) as max_num
    from (
        select customer_id, count(product_id) num
        from Orders
        group by customer_id, product_id) a
    group by customer_id
) a1 left join (
    select o.customer_id, o.product_id, count(o.product_id) num, p.product_name
    from Orders o left join Products p on o.product_id = p.product_id
    group by o.customer_id, o.product_id
) a2 on a1.customer_id = a2.customer_id and a1.max_num = a2.num

题目四：访问日期之间最大的空档期

题目要求：

假设今天的日期是 '2021-1-1' 。

编写解决方案，对于每个 user_id ，求出每次访问及其下一个访问（若该次访问是最后一次，则为今天）之间最大的空档期天数 window 。

返回结果表，按用户编号 user_id 排序。

1709. 访问日期之间最大的空档期 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

关于时间前后连接的题目大多数都用到了lead，关于这类题型我们之前做了很多，这里依旧重点讲解非窗口函数的解法。

首先我们先使用datediff（）函数算出所有日期之间的空档期。
select 
        u1.user_id, 
        u1.visit_date as visit_date1, 
        ifnull(u2.visit_date, "2021-1-1") as visit_date2, 
        datediff(ifnull(u2.visit_date, "2021-1-1"), u1.visit_date) as gap
    from UserVisits u1 left join UserVisits u2 on u1.user_id = u2.user_id and u1.visit_date < u2.visit_date
接着！我们按照 user_id、visit_date进行分组，由于多出的查询结果其实都是不满足“最大空档期”这个条件的，因此我们只要用一个max（）函数找到这些数据里的最大值就可以了。

运行代码示例：

with temp as 
(
    select 
        u1.user_id, 
        u1.visit_date as visit_date1, 
        ifnull(u2.visit_date, "2021-1-1") as visit_date2, 
        datediff(ifnull(u2.visit_date, "2021-1-1"), u1.visit_date) as gap
    from UserVisits u1 left join UserVisits u2 on u1.user_id = u2.user_id and u1.visit_date < u2.visit_date
)

select user_id, max(biggest_window) biggest_window
from (
    select user_id, visit_date1, min(gap) biggest_window 
    from temp
    group by user_id, visit_date1
) a group by user_id

题目五：向公司CEO汇报工作的所有人

题目要求：

编写解决方案，找出所有直接或间接向公司 CEO 汇报工作的职工的 employee_id 。

由于公司规模较小，经理之间的间接关系 不超过 3 个经理 。

可以以 任何顺序 返回无重复项的结果。

1270. 向公司 CEO 汇报工作的所有人 - 力扣（LeetCode）

表结构：

运行结果示例：

思路：

本题并不难，整体上使用了“套娃”的思想，因为题目中规定了经理之间的嵌套关系不超过三个，因此我们最多使用三层嵌套或者两次连接就可以做出来。

运行代码示例：

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

SELECT DISTINCT employee_id FROM (
    SELECT employee_id
    FROM Employees WHERE manager_id = 1
    UNION ALL
    SELECT employee_id
    FROM Employees WHERE manager_id IN (
        SELECT employee_id FROM Employees WHERE manager_id = 1
    )
    UNION ALL
    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
        )
    )
) T WHERE employee_id != 1

SELECT e1.employee_id
FROM Employees e1
JOIN Employees e2 ON e1.manager_id = e2.employee_id
JOIN Employees e3 ON e2.manager_id = e3.employee_id
WHERE e1.employee_id != 1 AND e3.manager_id = 1