sql-子查询

1978. 上级经理已离职的公司员工 - 力扣（LeetCode）

select t.employee_id 
from 
(
    select employee_id,
        manager_id,
        salary
    from Employees
    where salary  < 30000 AND
    manager_id is not null 
)t
left join Employees e
    on t.manager_id = e.employee_id 
where e.employee_id is null
order by t.employee_id

　　子表左连接，这种套路要烂熟于心

626. 换座位 - 力扣（LeetCode）

select 
(
    case 
    when id % 2 = 0 then id - 1
    when id % 2 = 1 and id < (select max(id) from Seat) then id + 1
    else id 
    end 

) as id,
student 
student from Seat
order by id

　　

当id为奇数时，如果不是最大的id就+1（与下一个偶数座位交换），如果是最大的id就保持不变（避免与不存在的座位交换）。

1341. 电影评分 - 力扣（LeetCode）

(select u.name as results
from Users u
left join MovieRating m
    using (user_id)
group by u.name 
ORDER BY count(m.movie_id) DESC, u.name
LIMIT 1)

UNION ALL

(SELECT m.title as results
FROM Movies   m
LEFT JOIN  MovieRating mr
        ON (m.movie_id = mr.movie_id) 
where YEAR(mr.created_at) = 2020 and 
        MONTH(mr.created_at) = 02
GROUP BY m.title
ORDER BY AVG(mr.rating) DESC, m.title 
LIMIT 1)

　　两种评分模式，union all 连接

1321. 餐馆营业额变化增长 - 力扣（LeetCode）

 

select visited_on,
    t.amount as amount,
    round(t.amount/7,2) as average_amount 
from 
(
    select distinct visited_on,
    sum(amount) over(order by visited_on range interval 6 day preceding) as amount
    from Customer 
)t
where date_sub(visited_on, interval 6 day) in (select visited_on from Customer)

子表查询和开窗函数，preceding之前，following之后

range interval x day 时间间隔

最后要有where限定，比较难想到

602. 好友申请 II ：谁有最多的好友 - 力扣（LeetCode）

select t.id as id,
    count(t.id) as num
from 
(
    select accepter_id as id
    from RequestAccepted
    union all
    select requester_id as id
    from RequestAccepted
)t
group by t.id
order by num desc
limit 1

　　requester_id 和 accepter_id 都算，不存在重复交叉的情况

所以要union all

585. 2016年的投资 - 力扣（LeetCode）

 

select round(sum(tiv_2016),2) as tiv_2016
from Insurance 
where tiv_2015 in 
(
    select tiv_2015 
    from Insurance
    group by tiv_2015
    having count(tiv_2015) >= 2
)
and (lat,lon) in 
(
    select lat,lon
    from Insurance
    group by lat,lon
    having count(*) = 1
)

　　两种条件，通过where... and来实现，唯不唯一通过count（*） = 1还是≥2来实现

还有一种方法开窗函数，开窗函数的使用还是很频繁的

select round(sum(t.tiv_2016),2) as tiv_2016
from 
(
    select tiv_2016,
        count(tiv_2015) over(partition by tiv_2015) as count1,
        count(*) over(partition by lat,lon) as count2
    from Insurance
)t
where t.count1 >= 2 and t.count2 = 1

185. 部门工资前三高的所有员工 - 力扣（LeetCode）

select d.name as Department,
    e.name as Employee,
    e.salary as Salary
from Employee e
left join Department d
    on e.departmentId = d.id
where 
(   select count(distinct e2.salary)
    from Employee e2
    where e2.salary > e.salary and 
    e.departmentId  = e2.departmentId 

) < 3
order by d.name, e.salary desc

　　在where里镶嵌子表查询，注意distinct去重

先以部门排序，然后才是工资降序