【SQL每日练习 难度中等】题源：LeetCode

LeetCode 177 第N高的薪水

表: Employee

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+

Id是该表的主键列。
该表的每一行都包含有关员工工资的信息。

编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null 。

查询结果格式如下所示。

示例 1:

输入:
Employee table:

+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

n = 2
输出:

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

示例 2:

输入:
Employee 表:

+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+

n = 2
输出:

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| null                   |
+------------------------+

解题思路

先用group by按薪水分组后再order by 降序排列，返回第n高的薪水.
注意要找到第n高的薪水，需要跳过n-1行，但无法直接用limit N-1，所以先在函数开头处理N为N=N-1。
limit和offset字段后面只接受正整数。

代码

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
set n:=n-1;
  RETURN (
      # Write your MySQL query statement below.
      select salary
      from Employee
      group by salary
      order by salary desc
      limit n,1
  );
END

---

LeetCode 176 第二高的薪水

Employee 表：

+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+

id 是这个表的主键。
表的每一行包含员工的工资信息。

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

查询结果如下例所示。

示例 1：

输入：
Employee 表：

+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

输出：

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

示例 2：

输入：
Employee 表：

+----+--------+
| id | salary |
+----+--------+
| 1  | 100    |
+----+--------+

输出：

+---------------------+
| SecondHighestSalary |
+---------------------+
| null                |
+---------------------+

相关知识点

LIMIT和OFFSET
limit 开始值m，结束值n，注意从第m+1行开始，查n行
limit 1就是第1行
limit 1,1是第2行，从第2行开始，查1行
limit 1,3是第2~4行

offset n 去掉几个值
limit 1 offset 3 查一行，跳过前三行

解题思路

将薪资按降序排序，然后使用 LIMIT 子句获得第二高的薪资。

代码

select( select distinct salary 
from employee
order by salary desc
LIMIT 1 offset 1) as 'SecondHighestSalary'

---

LeetCode 178 分数排名

表: Scores

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| score       | decimal |
+-------------+---------+

Id是该表的主键。
该表的每一行都包含了一场比赛的分数。Score是一个有两位小数点的浮点值。

编写 SQL 查询对分数进行排序。排名按以下规则计算:

分数应按从高到低排列。
如果两个分数相等，那么两个分数的排名应该相同。
在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

按 score 降序返回结果表。

查询结果格式如下所示。

示例 1:

输入:
Scores 表:

+----+-------+
| id | score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

输出:

+-------+------+
| score | rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

相关知识点

rank（）函数的使用
rank() over(业务逻辑)：成绩相同得排名是相同，下一位空出所占的名次，如1 1 3 3 5 6
dense_rank() over(业务逻辑)：成绩相同排名也相同，下一位接着下一个名次。如1 1 2 2 3 4
now_number() over(业务逻辑)：不需要考虑是否并列，即使根据条件查询出来的数值相同也会进行连续排序。如1 2 3 4 5 6

解题思路

用dense_rank() over(业务逻辑)符合题意

代码

select score, dense_rank() over (order by score desc) as 'rank'
from scores;

---

LeetCode 180 连续出现的数字

表：Logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+

id 是这个表的主键。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

返回的结果表中的数据可以按 任意顺序 排列。

查询结果格式如下面的例子所示：

示例 1:

输入：
Logs 表：

+----+-----+
| Id | Num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+

输出：
Result 表：

+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+

解释：1 是唯一连续出现至少三次的数字。

解题思路

题目问的连续三次出现，所以要用三个logs表检查。而且还要注意如果说连续出现超过三次，那么就会返回重复元素，所以要用distinct去重。

代码

select distinct l1.num as 'ConsecutiveNums'
from Logs l1, Logs l2, Logs l3
where l1.id = l2.id-1 
and l2.id = l3.id-1
and l1.num = l2.num
and l2.num = l3.num

---

LeetCode 184 部门工资最高的员工

表： Employee

+--------------+---------+
| 列名          | 类型    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+

id是此表的主键列。
departmentId是Department表中ID的外键。
此表的每一行都表示员工的ID、姓名和工资。它还包含他们所在部门的ID。

表： Department

+-------------+---------+
| 列名         | 类型    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+

id是此表的主键列。
此表的每一行都表示一个部门的ID及其名称。

编写SQL查询以查找每个部门中薪资最高的员工。
按 任意顺序 返回结果表。
查询结果格式如下例所示。

示例 1:

输入：
Employee 表:

+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Jim   | 90000  | 1            |
| 3  | Henry | 80000  | 2            |
| 4  | Sam   | 60000  | 2            |
| 5  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department 表:

+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+

输出：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Jim      | 90000  |
| Sales      | Henry    | 80000  |
| IT         | Max      | 90000  |
+------------+----------+--------+

解释：Max 和 Jim 在 IT 部门的工资都是最高的，Henry 在销售部的工资最高。

相关知识点

IN
IN 操作符允许您在 WHERE 子句中规定多个值。

解题思路

首先在Employee 表中查找在部门内查询最高工资，然后把表 Employee 和 Department 连接再在这张临时表里用 IN 语句查询部门名字和工资的关系。

代码

select Department.name as Department, Employee.name as Employee, salary
from Employee, Department
where Employee.departmentId = Department.id
and (Employee.departmentId,salary) 
in (select departmentId,max(salary)
from Employee
group by departmentId )