【SQL高频练习带刷】day7：子查询

        子查询可以用在SELECT、INSERT、UPDATE、DELETE语句中，以及在WHERE子句、HAVING子句或FROM子句中，以实现数据的关联和过滤。

题目一：上级经理已离职的公司员工

题目要求：

查找这些员工的id，他们的薪水严格少于$30000 并且他们的上级经理已离职。当一个经理离开公司时，他们的信息需要从员工表中删除掉，但是表中的员工的manager_id  这一列还是设置的离职经理的id 。

返回的结果按照employee_id 从小到大排序。

表结构：

 运行结果示例：

思路：

        只需要查询存在manager_id并且该id不在employee_id里的数据即可，再加上工资的限定条件。

运行代码示例：

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

题目二：换座位

题目要求：

编写解决方案来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。

按 id 升序 返回结果表。

表结构：

 运行结果示例：

思路：

        这道题乍一看有点难，但是我们坚信“不会做，就找规律”，观察示例，可以看出，除最后一名学生外，其余学生如果学号为奇数，则加一，如果为偶数，则减一。剩下的最后一名学生如果是奇数则不变，是偶数则符合上述规律。这么一看是不是就简单了很多？我们只需要判断id是否为奇数/偶数/奇数且等于总数这三种情况，分别讨论即可。但是我们需要先查询出总数，所以要用到子查询。

运行代码示例：

SELECT (CASE 
            WHEN MOD(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
            WHEN MOD(id,2) = 1 THEN id+1
            ElSE id-1
        END) AS id, student
FROM seat
ORDER BY id;

题目三：电影评分

题目要求：

请你编写一个解决方案：

• 查找评论电影数量最多的用户名。如果出现平局，返回字典序较小的用户名。
• 查找在 February 2020 平均评分最高 的电影名称。如果出现平局，返回字典序较小的电影名称。

字典序 ，即按字母在字典中出现顺序对字符串排序，字典序较小则意味着排序靠前。

表结构：

 运行结果示例：

思路：

        首先拆解题目，题目要求查找两部分内容，我们可以分别进行查找，然后union联合一下结果（注意重命名列名）。

        下一步是解决按照字典序返回的问题，其实这一步我们无需进行额外处理，直接order by 就可以了，我们用用户名和电影名作为排序字段时，默认会根据字典序进行排序。

        继而是对“平局”情况的处理，我们只需要先按照数量排序，再按照名称排序，使用limit子句限制查询结果为1即可。

        再来就没有什么难点了，两表连接查询就可以。需要注意的是测试用例中存在用户名与电影名相同的情况，此时使用union会去除掉重复结果，只查询出一个值，因此要使用union  all保留重复值。

运行代码示例：

# 找到评论数最多的人
(select name as results
from  MovieRating m
join Users u
using(user_id)
group by m.user_id
order by count(user_id) desc ,name
limit 1)
union all
# 找到平均评分最高的电影
(select title
from MovieRating
join Movies
using(movie_id)
where created_at between '2020-02-01' and '2020-02-29'
group by movie_id
order by avg(rating) desc, title 
limit 1)

题目四：好友申请--谁有更多的好友

题目要求：

编写解决方案，找出拥有最多的好友的人和他拥有的好友数目。

生成的测试用例保证拥有最多好友数目的只有 1 个人。

表结构：

 运行结果示例：

思路：

        这道题我一开始误解了题目的意思，还觉得这么简单的题也会出....果然是我天真了。题目给出的两个id分别是请求id和通过id，也就是申请好友--通过申请的过程，这样一条数据意味着两个id的用户成为了“彼此”的好有。因此，我们在统计时，也要兼顾两方。进一步，我们可以想到，我们只需要把所有的id都取出来，找出现次数最多的那个id即可。

        （本题目中，默认不存在1主动添加2,2又添加了1的情况，也就是不存在一对好友被计算了两遍；此外，表中所有数据一定是已通过的好友申请，不存在只申请未通过的情况。）

运行代码示例：

select requester_id id,count(accepter_id) num
from RequestAccepted
group by requester_id
order by count(accepter_id) desc
limit 1