MySQL面试题汇总（题源leetcode）

1.

第一种：分组查询＋count挑选
SELECT Email FROM Person GROUP BY Email HAVING count(Email) > 1
第二种：多表查询，找出两表id不同email相同的email
select distinct a.Email from Person a, Person b where a.Email = b.Email and a.Id != b.Id

2.某城市开了一家新的电影院，吸引了很多人过来看电影。该电影院特别注意用户体验，专门有个 LED显示板做电影推荐，上面公布着影评和相关电影描述。
作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。

答案：
select id,movie,description,rating from cinema where description!= ‘boring’ and id%2 = 1 order by rating desc

3.给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

答案
#update salary set sex = if(sex = ‘f’,‘m’,‘f’)
#update salary set sex = char(ascii(‘m’) + ascii(‘f’) - ascii(sex))
#update salary set sex =(case when sex=‘m’ then ‘f’ when sex=‘f’ then ‘m’ end);

4.Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

答案：
1.子查询，效率比较低
select Name Employee
from Employee a
where a.ManagerId is not null and a.Salary > (select Salary from Employee where Id = a.ManagerId)
2.左外
select e.Name as Employee
from Employee e
left join Employee e2 on e.ManagerId = e2.Id
where e.Salary > e2.Salary
3.普通多表查询
select e.Name as Employee
from Employee e ,Employee e2
where e.Salary > e2.Salary and e.ManagerId = e2.Id

答案：
select seller_id from sales group by seller_id having(
sum(price) >= all(select sum(price) from sales group by seller_id)
)

答案：
select max(salary) SecondHighestSalary from employee where salary < (
select max(salary) from employee
)

答案:
select player_id,device_id
from activity
where (player_id,event_date) in
(select player_id,min(event_date)
from activity
group by player_id)