176-第二高的薪水
第二高的薪水:https://leetcode-cn.com/problems/second-highest-salary/comments/
解法:
- 第二高的薪水,因此需要对查询结果作去重操作 distinct(Salary) ,然后对去重后的查询结果进行降序排列 order by Salary ,再通过 limit 1 offset 1 获取第二高的薪水值
- 因为需要输出 null ,因此需要在外面进行再次判断
select ifnull((
select distinct(Salary) from Employee # 去重查询
order by Salary desc # 降序排列
limit 1,1 # 获取第2名
), null) as SecondHighestSalary
177-第N高的薪水
第N高的薪水:https://leetcode-cn.com/problems/nth-highest-salary/
解法:
- 跟第一题一样的解法即可
- set n:=n-1; 或 set n=n-1; 为赋值语句
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N:=N-1;
RETURN (
# Write your MySQL query statement below.
select distinct(salary)
from employee
order by salary desc
limit N,1
);
END
175-组合两个表
组合两个表:https://leetcode-cn.com/problems/combine-two-tables/
解法:
- 根据题目,可知当Person表中不是每个PersonId在Address表中都有对应的字段,因此在连接时不能使用 where 子句
- 且在连接时不管Address表中是否有数据,每个PersonId都需要提供相关信息,因此需要使用左连接
select FirstName, LastName, City, State from Person as p
left join Address as a
on p.PersonId=a.PersonId;
178-分数排名
分数排名:https://leetcode-cn.com/problems/rank-scores/
编写一个 SQL 查询来实现分数排名。
解法1:
1、获取分数降序排列:
select Score from Scores order by Score desc
2、获取排名:
如果我们需要计算分数 x 的排名,我们可以先获取表中分数 >= x 的元素,然后进行去重,在进行计数,就得到了分数 x 的排名,如 98,97,96,94,93 ,我们要获取 96 的排名,先得到大于等于 96 的数据 98 ,97,96,然后计数为 3,因此 96 的排名为 3
select count(distinct Score) as `Rank` from Scores where Score >= x;
从结果的角度来看,第二部分的 Rank 是对应第一部分的分数来的,所以上面的 x 即为 a.score
select t1.Score,
(select count(distinct t2.Score) from Scores t2 where t2.score >= t1.score) as `Rank`
from Scores t1
order by t1.score desc;
或者写成:
select t1.Score,count(distinct t2.score) as `Rank`
from Scores t1,Scores t2
where t1.score <= t2.score
group by t1.id # t1的每条数据都要参与运算
order by t1.score desc
解法2:直接使用新版提供的窗口函数
select score,DENSE_RANK() over (ORDER BY SCORE DESC) as `RANK` from Scores;
窗口函数说明,现给定5个成绩:99,99,85,80,75
- DENSE_RANK()。如果使用 DENSE_RANK() 进行排名会得到:1,1,2,3,4。
- RANK()。如果使用 RANK() 进行排名会得到:1,1,3,4,5。
- ROW_NUMBER()。如果使用 ROW_NUMBER() 进行排名会得到:1,2,3,4,5。
180-连续出现的数字
连续出现的数字:https://leetcode-cn.com/problems/consecutive-numbers/
解法:
因为连续出现,所以不能使用 group by having 的思路解题
使用三表连接:
select distinct t1.Num as ConsecutiveNums
from Logs t1,Logs t2,Logs t3
where t1.Num=t2.Num and t2.Num=t3.Num and t1.id=t2.id-1 and t2.id=t3.id-1;
181-超过经理收入的员工
超过经理收入的员工:https://leetcode-cn.com/problems/employees-earning-more-than-their-managers/
解法:
# 通过where子句连接
select t1.Name Employee
from Employee t1,Employee t2
where t1.ManagerId=t2.id and t1.salary>t2.salary;
# 通过join子句连接
select t1.Name Employee from Employee t1
join Employee t2
on t1.ManagerId=t2.id and t1.Salary>t2.Salary;
182-查找重复的电子邮箱
查找重复的电子邮箱:https://leetcode-cn.com/problems/duplicate-emails/
解法:直接使用 group by email having count(email)>1
select Email from Person
group by Email
having count(Email) > 1;
183-从不订购的客户
从不订购的客户:https://leetcode-cn.com/problems/customers-who-never-order/
解法:
# 通过where子句进行查询
select Name Customers from Customers c
where c.id not in (select CustomerId from Orders) ;
184-部门工资最高的员工
部门工资最高的员工:https://leetcode-cn.com/problems/department-highest-salary/
解法:
# 先查询每个部门的最高工资
select DepartmentId,max(salary)
from Employee group by DepartmentId;
# 分别查询出对应的数据
select t1.Name Department,t2.Name Employee,Salary
from Department t1 join Employee t2
on t1.Id=t2.DepartmentId # 连表
where (t2.DepartmentId,t2.Salary) in ( # 筛选条件
select DepartmentId,max(salary)
from Employee
group by DepartmentId
);
185-部门工资前三高的所有员工
部门工资前三高的所有员工:https://leetcode-cn.com/problems/department-top-three-salaries/
解法:
# 获取不超过3个的employee的Id和Salary
select t1.Name as Employee,t1.Salary from Empolyee t1
where 3 > (
select count(distinct t2.Salary)
from Employee t2 where t2.Salary > t1.Salary
);
# 连接Department表获取部门信息
select t3.Name Department,t1.Name as Employee,t1.Salary
from Employee t1 join Department t3
on t3.id=t1.DepartmentId
where 3 > (
select count(distinct t2.Salary)
from Employee t2
where t2.Salary > t1.Salary and t1.DepartmentId=t2.DepartmentId
);
196-删除重复的电子邮箱
删除重复的电子邮箱:https://leetcode-cn.com/problems/delete-duplicate-emails/
解法:
delete t1 from Person t1
join Person t2
on t1.email = t2.email and t1.id > t2.id;