整理几道面试常考sql题

1、分数排名

力扣：[力扣地址](https://leetcode.cn/problems/rank-scores/)

表：Scores
±------------±--------+
| Column Name | Type |
±------------±--------+
| id | int |
| score | decimal |
±------------±--------+
Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。

编写 SQL 查询对分数进行排序。排名按以下规则计算:
1、分数应按从高到低排列。
2、如果两个分数相等，那么两个分数的排名应该相同。
3、在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

第一种方法：使用窗口函数（dense_rank()：对结果集进行排序，排名值没有间断。 特定行的排名等于该特定行之前不同排名值的数量加一。）

SELECT Score,
	dense_rank() over(order by Score desc) as 'Rank'
FROM Scores

第二种方法： 1.从两张相同的表scores分别命名为s1，s2。 2.s1中的score与s2中的score比较大小。意思是在输出s1.score的前提下，有多少个s2.score大于等于它。比如当s1.salary=3.65的时候，s2.salary中[4.00,4.00,3.85,3.65,3.65]有5个成绩大于等于他，但是利用count(distinct s2.score)去重可得s1.salary3.65的rank为3 3.group by s1.id 不然的话只会有一条数据 4.最后根据s1.score排序desc

select s1.score, count(distinct s2.score) as 'rank'
from scores as s1,scores as s2
where s1.score<=s2.score
group by s1.id
order by s1.score desc;

2、部门工资前三高的所有员工

力扣：[力扣地址](https://leetcode.cn/problems/department-top-three-salaries/)

表: Employee
±-------------±--------+
| Column Name | Type |
±-------------±--------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
±-------------±--------+
Id是该表的主键列。
departmentId是Department表中ID的外键。
该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。

表: Department

±------------±--------+
| Column Name | Type |
±------------±--------+
| id | int |
| name | varchar |
±------------±--------+
Id是该表的主键列。
该表的每一行表示部门ID和部门名。

SELECT
	Department.NAME AS Department, --取部门名称
	e1.NAME AS Employee, --取员工姓名
	e1.Salary AS Salary --取工资值
FROM
	Employee AS e1,Department
WHERE
	e1.DepartmentId = Department.Id 
--从这里开始是筛选部门前三高的工资
--筛选方法：从e2表里寻找在相同部门中比自身(e1表)更高的工资(值)，
--像这样的工资(值)一共不超过3个（即只有0,1,2个）
	AND 3 > --不超过3个（即只有0,1,2个）
            (SELECT  count( DISTINCT e2.Salary ) --像这样的工资(值)一共
             FROM Employee AS e2 --从e2表里寻找
             WHERE e1.Salary < e2.Salary --比自身(e1表)更高的工资(值)
             AND e1.DepartmentId = e2.DepartmentId) --在相同部门中

--筛选完成
ORDER BY Department.NAME,Salary DESC; --根据部门、工资值倒序排序

使用窗口函数
select Department, Employee, Salary
from (
    select d.Name as Department, e.Name as Employee, e.Salary as Salary, 
dense_rank() over ( partition by DepartmentId order by Salary desc) as rk
    from Employee as e, Department as d
    where e.DepartmentId = d.Id
) m
where rk <= 3;

3、第二高的薪水

力扣：力扣题

Employee 表：

±------------±-----+
| Column Name | Type |
±------------±-----+
| id | int |
| salary | int |
±------------±-----+
id 是这个表的主键。
表的每一行包含员工的工资信息。

编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null 。

select ifNull((
	select distinct Salary
	from Employee
	order by Salary desc 
	limit 1,1),null) as SecondHighestSalary