高频 sql 50题（基础6）

六、子查询

1.上级经理已经离职的公司员工

题目描述

代码编写

select employee_id from Employees 
where salary<30000 and manager_id not in (select employee_id from Employees)
order by employee_id

2.换座位

题目描述

代码编写

select a.id,ifnull(b.student,a.student) as student
from Seat a left join Seat b 
on (case when mod(a.id,2)=1 then a.id=b.id-1 else a.id=b.id+1 end)

思路：

利用自连接，将表看成表a、表b，当id不能被2整除时，a.id=b.id-1(对应a.id=1,b.id=2)；当id能被2整除时，a.id=b.id+1(对应a.id=2,b.id=1),完成学生1和学生2的座位互换，以此类推，筛选出a.id和b.student（a.id顺序输出，b.student是互换后的结果）。同时，当id为奇数时，最后一个学生没有可以互换座位，b.student输出结果为null，需要再加一个ifnull（）判断，当输出结果为null，输出a.studnet即可。

3.电影评分

题目描述

代码编写

(select b.name as results 
from MovieRating as a left join Users b
on a.user_id=b.user_id
group by a.user_id
order by count(a.user_id) desc,b.name 
limit 1 )
union all
(select d.title  as results 
from MovieRating as c left join Movies d
on c.movie_id=d.movie_id
where c.created_at like "2020-02-%" 
group by c.movie_id
order by avg(c.rating) desc,d.title
limit 1)

Tip：

这道题使用的函数是 union all 而不是 union ，因为union all 遇到重复值也会保存下来，而union不会保留重复值，因此部分测试用例无法通过，如以下所示：

4.餐馆营业额变化增长

题目描述

代码编写

做法一（自连接）：

select a.visited_on,
sum(b.amount) as amount,
round(sum(b.amount)/7,2) as average_amount
from(select distinct visited_on from Customer) a join Customer b 
on datediff(a.visited_on,b.visited_on) between 0 and 6
where a.visited_on>=(select min(visited_on) from Customer)+6
group by a.visited_on
order by a.visited_on

 思路：筛选出不重复的日期作为表a，与原表b连接，连接条件为表b日期在表a日期的6天前内，显示每个7天的信息（即日期与最小日期的差值大于或等于6），并计算消费总额和平均值。

做法二（利用窗口函数）：

select 
visited_on,
total as amount,
round((total/7),2) as average_amount 
from(
    select 
    distinct visited_on,
    sum(amount) over(order by visited_on range interval 6 day preceding)as total 
    from Customer
    order by visited_on)as a
where datediff(visited_on,(select min(visited_on) from Customer))>=6

思路：窗口函数的思路和自连接的思路一致 ，它是直接计算每个当前日期以及前六天的消费总额（即使前面的数据不够，窗口函数也会将范围内的数据框住并计算），再筛选出每个第7天的信息。

5.好友申请

题目描述

代码编写

select ids as id,count(ids) as num from(
    select requester_id as ids from RequestAccepted
    where accept_date is not null
    union all
    select accepter_id as ids from RequestAccepted
    where accept_date is not null)as a
group by  ids
order by num desc
limit 1

思路：用 union all 函数将所有的请求的发起者和接受者联合在一列上，并按照 id 分组并统计每个id 出现的次数（要判断好友申请是否通过，即是否有接受好友申请日期），将统计结果倒序排序并取出第一条信息（即好友数量最多）。

6.2016年的投资

题目描述

代码编写

select round(sum(distinct a.tiv_2016),2) as tiv_2016 
from Insurance a join Insurance b
on a.pid!=b.pid and a.tiv_2015=b.tiv_2015
where (a.lat,a.lon) not in (
    select lat,lon from Insurance c
    where a.pid!=c.pid)

注意：要用 distinct 函数确保取得的 a.tiv_2016 不会重复，如果没有用 distinct 函数，a.tiv_2016分别会取到重复数据，在 a.ip=1 的时候筛选到 tiv_2016为5 和40，而在 a.ip=4 的时候筛选到  tiv_2016 为40 和5。

7.部门工资前三高的所有员工

题目描述

 

代码编写

select Department, Employee, Salary from (
    select d.name as Department ,
     e.name as Employee , 
     e.salary as Salary , 
     dense_rank() over(partition by d.name order by e.salary desc) as rk 
     from Employee e left join Department d on e.departmentId = d.id) as a
     where a.rk <= 3;

思路：利用窗口函数对每个部门的工资高低进行排序，筛选出每个部门工资排名前三的信息。