【MySQL】-4 练习题

本周整理几个leetcode题目

1. 排名题：编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”(letcode 178)

   
   image.png

 select Score,(select count(distinct score) 
               from Scores WHERE score>=s.score) 
               as Rank from Scores s 
order by Score desc;

首先思考排名列，由于分数相同排名也相同（用distinct区别），我们可以根据分数进行降序，>=该数值的不同分数有几个，这里的个数就是对应的分数排名。比如3.85，>=3.85的不同分数是4.00,3.85，有两个，因此3.85排名第二。同理可推。
因此，rank对应的数值，就是>=该分数的不同分数的个数——count（distinct（score））。

2. 编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
   方法一：自联结

select distinct a.Email from Person a,
Person b where a.Email=b.Email
and a.Id!=b.Id;

以不同Email地址的表作为基础表，包含所有信息的表作为匹配表。选择基础表中Email信息与匹配表相同（重复），但是id不同的地址信息呈现。

方法二：group by

select Email from Person
group by Email
having count(Email)>1

用group by 统计不同email出现的次数。过滤那些次数超过一次（重复）的信息。

3. 求出每个部门工资最高的员工信息。

下面是员工表（Employee）和部门表（Department），以及需要呈现的结果。

image.png image.png

方法一：子查询

select 
t.Name as Department,
e.Name as Employee,
e.Salary as Salary
from(
      select DepartmentId,
        d.Name as Name,
        max(Salary) as Salary
        from Employee e
      join Department d on d.Id=e.DepartmentId
      group by DepartmentId) as t
join Employee e
on e.DepartmentId=t.DepartmentId
and e.Salary=t.Salary;

思考：答案需要呈现的是，不同部门最高工资以及对应的员工信息。因此关键在于求出这个最高工资的人是谁，剩下信息join一下即可。
1）找到部门最高工资的人，那就要根据部门group by 求出工资最大值。
2）里面的查询结果为：
["Department","Employee","Salary"]
[["Sales","Henry",80000]
["IT","Max",90000]]
3)join 得到员工姓名和其余信息。

方法二：

select d.Name as Department, e.Name as Employee, e.Salary
from Department d 
inner join Employee e 
on d.Id = e.DepartmentId 
and e.Salary >= 
(select max(Salary) 
 from Employee where DepartmentId = d.Id)