以下题目均来自leetcode官网 https://leetcode.cn/
本文仅用于记录学习进度
185. 部门工资前三高的所有员工
#利用开窗函数
select d.name Department, t.name Employee, t.salary Salary
from (select *, rank() over (partition by departmentId order by salary desc) rk
from employee) t
left join department d on t.departmentId = d.id
where rk <= 3
;
# 普通做法
select d.name Department,
e1.name,
e1.salary
from employee e1,
employee e2,
department d
where e1.salary <= e2.salary
and e1.departmentId = e2.departmentId
and e1.departmentId = d.id
group by e1.departmentId, e1.name
having count(distinct e2.salary) <= 3
1767. 寻找没有被执行的任务对
with recursive t as
(
select task_id, subtasks_count
from Tasks # 会逐行遍历Tasks作为初始结果 第一次遍历第一行
union
select task_id, subtasks_count - 1
from t
where subtasks_count > 1 # 将初始结果进行递归
)
select t.task_id, subtasks_count subtask_id
from t
where (task_id, subtasks_count) not in (select * from executed)
;
1384. 按年度列出销售总额
编写一段 SQL 查询每个产品每年的总销售额,并包含 product_id, product_name 以及 report_year 等信息。
销售年份的日期介于 2018 年到 2020 年之间。你返回的结果需要按product_id 和 report_year 排序。
# 目标:一条数据的期间年份 划分成 多个连续的期间
# 思路: 利用递归找出最大年份到最小年份中间的年份,但此时的start都为同一月份和号数,所以对统一id进行排序,排名不为1的 统一将日期替换为 该年的 一月一号
# 当start的年份不等于end的年份,将end的年份替换成start年份 加 '-12-31'。至此期间划分完成
# 关联product表,进行计算 本体结束
with recursive t as (
select product_id, period_start, period_end, average_daily_sales
from sales
union
select product_id,
date_add(period_start, interval 1 year),
period_end,
average_daily_sales
from t
where period_start < period_end
)
select product_id,
product_name,
SUBSTRING(period_start, 1, 4) report_year,
abs(datediff(new_period_end, ADDDATE(new_period_start, -1)) * average_daily_sales) total_amount
from (select a.product_id,
period_start,
period_end,
average_daily_sales,
rk,
product_name,
if(rk = 1, period_start, concat(substring(period_start, 1, 4), '-01-01')) new_period_start,
if((substring(period_start, 1, 4) != substring(period_end, 1, 4)),
concat(substring(period_start, 1, 4), '-12-31'), period_end) new_period_end
from (select *, rank() over (partition by product_id order by period_start) rk
from t) a
left join product on product.product_id = a.product_id
where substring(period_start, 1, 4) <= substring(period_end, 1, 4)) b;