力扣Mysql练手

最近在刷LeetCode的SQL题练手，在此总结一下，本文篇幅比较长，基本刷完了力扣Mysql非会员的题

175 组合两个表

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

请注意，你必须编写一个 Update 语句，不要编写任何 Select 语句。

例如:

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

运行你所编写的更新语句之后，将会得到以下表:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

MySQL脚本：

-- ----------------------------
-- Table structure for `salary`
-- ----------------------------
DROP TABLE IF EXISTS `salary`;
CREATE TABLE `salary` (
  `id`int(11) NOT NULL,
  `name`varchar(10) NOT NULL,
  `sex`varchar(10) NOT NULL,
 `salary` int(11) NOT NULL,
  PRIMARYKEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of salary
-- ----------------------------
INSERT INTO `salary` VALUES ('1', 'A', 'm','2500');
INSERT INTO `salary` VALUES ('2', 'B', 'f','1500');
INSERT INTO `salary` VALUES ('3', 'C', 'm','5500');
INSERT INTO `salary` VALUES ('4', 'D', 'f','500');

本题解法：

方法一：使用 if 函数

IF(expr1, expr2, expr3) 如果 expr1 为 TRUE，则 IF () 的返回值为 expr2；否则返回值为 expr3。

# Write your MySQL query statement below
UPDATE salary
SET sex = IF(sex = "f","m","f");

方法二：使用 case…when…then…else…end

UPDATE salary 
SET sex  = (CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END);

176 - 第二高的薪水

• 题目描述
  编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）。

Id	Salary
1	100
2	200
3	300

例如上述 Employee 表，SQL 查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

SecondHighestSalary
200

本题解法

方法 1 简便方法，倒序排序，limit(1,1)即可

SELECT IFNULL((SELECT DISTINCT(Salary) 
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1),null) AS SecondHighestSalary

方法 2
先查询出最高的身高值，然后查询身高小于该值的最高身高。

# Write your MySQL query statement below
SELECT Max(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT Max(Salary) FROM Employee);

注：IFNULL() 方法介绍：

IFNULL(expr1,expr2)

如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2。IFNULL() 返回一个数字或字符串值，取决于它被使用的上下文环境。

177 - 第 n 高的薪水

• 题目描述
  编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

Id	Salary
1	100
2	200
3	300

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

getNthHighestSalary(2)
200

MySQL 脚本

Create table If Not Exists Employee (Idint, Salary int);
Truncate table Employee;
insert into Employee (Id, Salary) values('1', '100');
insert into Employee (Id, Salary) values('2', '200');
insert into Employee (Id, Salary) values('3', '300');

本题解法

查询条件：

• 1）返回第 N 高的薪水
• 2）如果不存在第 N 高的薪水，那么查询应返回 null

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT(
      SELECT DISTINCT Salary
      FROM Employee
      ORDER BY Salary DESC
      LIMIT 1 OFFSET N
      )
  );
END

注意：LIMIT 子句后面不能做运算。

或者可以新定义一个变量 x：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE x int;
  SET x = N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT(
      SELECT DISTINCT Salary
      FROM Employee
      ORDER BY Salary DESC
      LIMIT 1 OFFSET x
      )
  );
END

178 - 分数排名

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

Id	Score
1	3.50
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

Score	Rank
4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

MySQL脚本

Create table If Not Exists Scores (Id int,Score DECIMAL(3,2));
Truncate table Scores;
insert into Scores (Id, Score) values ('1','3.5');
insert into Scores (Id, Score) values ('2','3.65');
insert into Scores (Id, Score) values ('3','4.0');
insert into Scores (Id, Score) values ('4','3.85');
insert into Scores (Id, Score) values ('5','4.0');
insert into Scores (Id, Score) values ('6','3.65');

本题解法：

• 方法 1
  对于每一个分数，从表中找出有多少个大于或等于该分数的不重复分数，然后降序排列。

# Write your MySQL query statement below
SELECT Score,(
    SELECT COUNT(DISTINCT s.Score)
    FROM Scores s
    WHERE s.Score >= Scores.Score) AS Rank
FROM Scores
ORDER BY Score DESC;

这种方式虽然很简单，但是在 select 语句中使用子查询来生成 rank，相当于对每一条记录都需要查询一次 scores 表，查询次数为 （记录条数 ^2），会很慢。

• 方法2
  在 select 语句中创建两个变量，其中 @PreScore 用来记录前一个分数，@Ranker 用来记录当前排名。然后判断当前的分数是否等于前面的分数 @PreScore，如果等于的话，排名值就为 @Ranker，否则，排名值为 @Ranker+1。

这里用了 CAST() 函数进行类型转换，其语法为：

CAST(字段名 as 转换的类型 )

其中类型可以为：

• CHAR [(N)] 字符型
• DATE 日期型
• DATETIME 日期和时间型
• DECIMAL float 型
• SIGNED int
• TIME 时间型

SELECT Score,CAST(
    CASE 
    WHEN @PreScore = Score THEN @Ranker
    WHEN @PreScore := Score THEN @Ranker := @Ranker + 1
    ELSE @Ranker := @Ranker + 1
  END AS SIGNED) AS Rank
FROM Scores a,(SELECT @PreScore := NULL, @Ranker := 0) r
ORDER BY Score DESC;

180 - 连续出现的数字

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

Id	Num
1	1
2	1
3	1
4	2
5	1
6	2
7	2

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

ConsecutiveNums
1

• MySQL 脚本

Create table If Not Exists Logs (Id int,Num int);
Truncate table Logs;
insert into Logs (Id, Num) values ('1','1');
insert into Logs (Id, Num) values ('2','1');
insert into Logs (Id, Num) values ('3', '1');
insert into Logs (Id, Num) values ('4','2');
insert into Logs (Id, Num) values ('5','1');
insert into Logs (Id, Num) values ('6','2');
insert into Logs (Id, Num) values ('7','2');

本题解法：

由于需要找三次相同数字，所以我们需要建立三个表的实例，我们可以用 l1 分别和 l2, l3 内交，l1 和 l2 的 Id 下一个位置比，l1 和 l3 的下两个位置比，然后将 Num 都相同的数字返回。

注意： SELECT DISTINCT l1.Num AS ConsecutiveNums 保证当连续的数字大于 3 个的时候，返回的还是一个 Num。

# Write your MySQL query statement below
SELECT DISTINCT l1.Num AS ConsecutiveNums
FROM Logs l1 
LEFT JOIN Logs l2
ON l1.Id = l2.Id - 1
LEFT JOIN Logs l3
ON l1.Id = l3.Id - 2
WHERE l1.Num = l2.Num AND l1.Num = l3.Num;

181 - 超过经理收入的员工

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

Id	Name	Salary	ManagerId