sql进阶day02

以下题目均来自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;