子查询可以用在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