LeetCode题目总结-SQL

我最近总结了LeetCode下所有SQL题的多种解法。

175-组合两个表

题目描述

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

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

例如:

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

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

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

MySQL脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- ----------------------------
-- 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。

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

方法二:使用case…when..then..else..end

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

176-第二高的薪水

题目描述

编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。

1
2
3
4
5
6
7
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+

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

1
2
3
4
5
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+

本题回答

方法1

先查询出最高的身高值,然后查询身高小于该值的最高身高。

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

方法2

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

这里加个IFNULL,只是为了可读性好一点?

注:IFNULL()方法介绍:

1
IFNULL(expr1,expr2)

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

177-第n高的薪水

题目描述

编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。

1
2
3
4
5
6
7
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+

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

1
2
3
4
5
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+

MySQL脚本

1
2
3
4
5
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
1
2
3
4
5
6
7
8
9
10
11
12
13
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。

1
2
3
4
5
6
7
8
9
10
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+

例如,根据上述给定的 Scores 表,你的查询应该返回(按分数从高到低排列):

1
2
3
4
5
6
7
8
9
10
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+

MySQL脚本

1
2
3
4
5
6
7
8
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

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

1
2
3
4
5
6
7
# 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()函数进行类型转换,其语法为:

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

其中类型可以为:

  • CHAR[(N)] 字符型
  • DATE 日期型
  • DATETIME 日期和时间型
  • DECIMAL float型
  • SIGNED int
  • TIME 时间型
1
2
3
4
5
6
7
8
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 查询,查找所有至少连续出现三次的数字。

1
2
3
4
5
6
7
8
9
10
11
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+

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

1
2
3
4
5
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+

MySQL脚本

1
2
3
4
5
6
7
8
9
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

1
2
3
4
5
6
7
8
# 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。

1
2
3
4
5
6
7
8
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+

给定 Employee 表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工。

1
2
3
4
5
+----------+
| Employee |
+----------+
| Joe |
+----------+

MySQL脚本

1
2
3
4
5
6
Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, ManagerId int);
Truncate table Employee;
insert into Employee (Id, Name, Salary, ManagerId) values ('1', 'Joe', '70000', '3');
insert into Employee (Id, Name, Salary, ManagerId) values ('2', 'Henry', '80000', '4');
insert into Employee (Id, Name, Salary, ManagerId) values ('3', 'Sam', '60000', null);
insert into Employee (Id, Name, Salary, ManagerId) values ('4', 'Max', '90000', null);

本题回答

我们可以生成两个实例对象通过ManagerId和Id进行INNER JOIN,然后限制条件是一个Salary大于另一个。

1
2
3
4
# Write your MySQL query statement below
SELECT b.Name AS Employee
FROM Employee a INNER JOIN Employee b
ON a.Id = b.ManagerId AND b.Salary > a.Salary;

182-查找重复的电子邮箱

题目描述

编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。

示例:

1
2
3
4
5
6
7
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+

根据以上输入,你的查询应返回以下结果:

1
2
3
4
5
+---------+
| Email |
+---------+
| a@b.com |
+---------+

说明:所有电子邮箱都是小写字母。

MySQL脚本

1
2
3
4
5
Create table If Not Exists Person (Id int,Email varchar(255));
Truncate table Person;
insert into Person (Id, Email) values ('1','a@b.com');
insert into Person (Id, Email) values ('2','c@d.com');
insert into Person (Id, Email) values ('3','a@b.com');

本题回答

寻找重复的数据:用group by Email分组后 数据个数大于1的就是重复的。

1
2
3
4
5
# Write your MySQL query statement below
SELECT Email
FROM Person
GROUP BY Email
HAVING Count(*) >= 2;

183-从不订购的客户

题目描述

某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。

Customers 表:

1
2
3
4
5
6
7
8
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+

Orders 表:

1
2
3
4
5
6
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+

例如给定上述表格,你的查询应返回:

1
2
3
4
5
6
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+

MySQL脚本

1
2
3
4
5
6
7
8
9
10
Create table If Not Exists Customers (Idint, Name varchar(255));
Create table If Not Exists Orders (Id int,CustomerId int);
Truncate table Customers;
insert into Customers (Id, Name) values('1', 'Joe');
insert into Customers (Id, Name) values('2', 'Henry');
insert into Customers (Id, Name) values('3', 'Sam');
insert into Customers (Id, Name) values('4', 'Max');
Truncate table Orders;
insert into Orders (Id, CustomerId) values('1', '3');
insert into Orders (Id, CustomerId) values('2', '1');

本题回答

方法1——LEFT JOIN

直接让两个表左外连接,然后只要找出右边的CustomerId为Null的顾客就是没有下订单的顾客。

1
2
3
4
SELECT Customers.Name AS Customers
FROM Customers LEFT JOIN Orders
ON Customers.Id = Orders.CustomerId
WHERE Orders.CustomerId IS null;

方法2——NOT IN

1
2
3
4
5
6
SELECT c.Name AS Customers
FROM Customers AS c
WHERE c.Id NOT IN (
SELECT DISTINCT CustomerId
FROM Orders
);

184-部门最高工资

题目描述

Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。

1
2
3
4
5
6
7
8
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

1
2
3
4
5
6
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+

编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。

1
2
3
4
5
6
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+

MySQL脚本

1
2
3
4
5
6
7
8
9
10
Create table If Not Exists Employee (Idint, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Idint, Name varchar(255));
Truncate table Employee;
insert into Employee (Id, Name, Salary,DepartmentId) values ('1', 'Joe', '70000', '1');
insert into Employee (Id, Name, Salary,DepartmentId) values ('2', 'Henry', '80000', '2');
insert into Employee (Id, Name, Salary,DepartmentId) values ('3', 'Sam', '60000', '2');
insert into Employee (Id, Name, Salary,DepartmentId) values ('4', 'Max', '90000', '1');
Truncate table Department;
insert into Department (Id, Name) values('1', 'IT');
insert into Department (Id, Name) values('2', 'Sales');

本题回答

通过GROUP BY把每个部门的最高工资找出来,再通过联结表,找到相应的员工名字和部门名字。

1
2
3
4
5
6
7
# Write your MySQL query statement below
SELECT d.name AS Department, e.name AS Employee, e.Salary AS Salary
FROM Employee e,Department d
WHERE e.DepartmentId = d.Id
AND ((e.DepartmentId,e.Salary) IN (SELECT DepartmentId,Max(Salary) AS MSalary
FROM Employee
GROUP BY DepartmentId));

185-部门工资前三高的员工

题目描述

Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id 。

1
2
3
4
5
6
7
8
9
10
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

1
2
3
4
5
6
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+

编写一个 SQL 查询,找出每个部门工资前三高的员工。例如,根据上述给定的表格,查询结果应返回:

1
2
3
4
5
6
7
8
9
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+

MySQL脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, DepartmentId int);
Create table If Not Exists Department (Id int, Name varchar(255));
Truncate table Employee;
insert into Employee (Id, Name, Salary, DepartmentId) values ('1', 'Joe', '85000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('2', 'Henry', '80000', '2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('3', 'Sam', '60000', '2');
insert into Employee (Id, Name, Salary, DepartmentId) values ('4', 'Max', '90000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('5', 'Janet', '69000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('6', 'Randy', '85000', '1');
insert into Employee (Id, Name, Salary, DepartmentId) values ('7', 'Will', '70000', '1');
Truncate table Department;
insert into Department (Id, Name) values ('1', 'IT');
insert into Department (Id, Name) values ('2', 'Sales');

本题回答

方法1

1
2
3
4
5
6
7
8
9
SELECT d.Name AS Department,e1.Name AS Employee,e1.Salary AS Salary
FROM Employee AS e1 INNER JOIN Department AS d
ON e1.DepartmentId = d.Id
WHERE (
SELECT COUNT(DISTINCT e2.Salary)
FROM Employee AS e2
WHERE e2.DepartmentId = d.Id AND e2.Salary >= e1.Salary
) <= 3
ORDER BY e1.DepartmentId,e1.Salary DESC;

方法2

补充一个提交注意点,部门薪水前三高包含了相同薪水下排名相同的意思

1、问题

查询出每个部门 薪水前三高 的员工。我们可以分解查询步骤,再组合。

2、简化问题(分步骤)

分解步骤的思路,可以依据必要存在的步骤进行分解

1.根据 部门 (升),薪水 (降) 顺序查询出每个部门的员工 (Department, Employee, Salary)

1
2
3
4
SELECT dep.Name AS Department, emp.Name AS Employee, emp.Salary
FROM Employee AS emp INNER JOIN Department AS dep
ON emp.DepartmentId = dep.Id
ORDER BY emp.DepartmentId, emp.Salary DESC

2.每个部门的员工根据薪水进行排序

由于原本没有排序的字段,所以这里就需要自定义变量补充一个字段出来

1
2
3
4
5
6
7
8
9
10
11
12
SELECT te.DepartmentId, te.Salary,
CASE
WHEN @pre = DepartmentId THEN @rank:= @rank + 1
WHEN @pre := DepartmentId THEN @rank:= 1
END AS RANK
FROM (SELECT @pre:=null, @rank:=0)tt,
(
SELECT DepartmentId,Salary
FROM Employee
GROUP BY DepartmentId,Salary
ORDER BY DepartmentId,Salary DESC
) te
1
2
3
4
5
6
7
8
9
10
+--------------+--------+------+
| DepartmentId | Salary | RANK |
+--------------+--------+------+
| 1 | 90000 | 1 |
| 1 | 85000 | 2 |
| 1 | 70000 | 3 |
| 1 | 69000 | 4 |
| 2 | 80000 | 1 |
| 2 | 60000 | 2 |
+--------------+--------+------+
3、组合步骤

组合步骤时,尽量将每个步骤变成一个 结果集(不存在二次查询),再将所有步骤的 结果集进行关联,从而提高性能。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT dep.Name Department, emp.Name Employee, emp.Salary
FROM (
SELECT te.DepartmentId, te.Salary,
CASE
WHEN @pre = DepartmentId THEN @rank:= @rank + 1
WHEN @pre := DepartmentId THEN @rank:= 1
END AS RANK
FROM (SELECT @pre:=null, @rank:=0)tt,
(
SELECT DepartmentId,Salary
FROM Employee
GROUP BY DepartmentId,Salary
ORDER BY DepartmentId,Salary DESC
)te
)t
INNER JOIN Department dep
ON t.DepartmentId = dep.Id
INNER JOIN Employee emp
ON t.DepartmentId = emp.DepartmentId and t.Salary = emp.Salary and t.RANK <= 3
ORDER BY t.DepartmentId, t.Salary DESC
1
2
3
4
5
6
7
8
9
10
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Joe | 85000 |
| IT | Randy | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+

196-删除重复的电子邮箱

题目描述

编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。

1
2
3
4
5
6
7
8
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id 是这个表的主键。

例如,在运行你的查询语句之后,上面的 Person 表应返回以下几行:

1
2
3
4
5
6
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+

MySQL脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- ----------------------------
-- Table structure for `person`
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
`Id` int(11) DEFAULT NULL,
`Email` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of person
-- ----------------------------
INSERT INTO `person` VALUES ('1','john@example.com');
INSERT INTO `person` VALUES ('2','bob@example.com');
INSERT INTO `person` VALUES ('3','john@example.com');

本题回答

  • 查询目标:删除一条记录
  • 查询范围:Person表
  • 查询条件:删除所有重复的电子邮箱 ,重复的邮箱里只保留Id最小的哪个。

显然,通过这个查询条件可以提取出来两条and关系的条件:

  • 找出所有重复的电子邮箱
  • 删除Id大的重复邮箱
  • 对于条件(1),需要判断出所有重复的电子邮箱,即p1.Email = p2.Email
  • 对于条件(2),需要判断重复邮箱中Id较大的:p1.Id > p2.Id
1
2
3
4
# Write your MySQL query statement below
DELETE p1
FROM Person p1,Person p2
WHERE (p1.Email = p2.Email) AND (p1.Id > p2.Id);

197-上升的温度

题目描述

给定一个 Weather 表,编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 Id。

1
2
3
4
5
6
7
8
+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------------+------------------+

例如,根据上述给定的 Weather 表格,返回如下 Id:

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

MySQL脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- ----------------------------
-- Table structure for `weather`
-- ----------------------------
DROP TABLE IF EXISTS `weather`;
CREATE TABLE `weather` (
`Id` int(11) DEFAULT NULL,
`RecordDate` date DEFAULT NULL,
`Temperature` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of weather
-- ----------------------------
INSERT INTO `weather` VALUES ('1','2015-01-01', '10');
INSERT INTO `weather` VALUES ('2','2015-01-02', '25');
INSERT INTO `weather` VALUES ('3','2015-01-03', '20');
INSERT INTO `weather` VALUES ('4','2015-01-04', '30');

本题答案

我们可以使用MySQL的函数DATEDIFF来计算两个日期的差值,我们的限制条件是温度高且日期差1。

1
2
3
4
# Write your MySQL query statement below
SELECT a.Id
FROM Weather a INNER JOIN Weather b
WHERE DATEDIFF(a.RecordDate,b.RecordDate) = 1 AND a.Temperature > b.Temperature;

595-大的国家

题目描述

这里有张 World 表

1
2
3
4
5
6
7
8
9
+-----------------+------------+------------+--------------+---------------+
| name | continent | area | population | gdp |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
+-----------------+------------+------------+--------------+---------------+

如果一个国家的面积超过300万平方公里,或者人口超过2500万,那么这个国家就是大国家。

编写一个SQL查询,输出表中所有大国家的名称、人口和面积。

例如,根据上表,我们应该输出:

1
2
3
4
5
6
+--------------+-------------+--------------+
| name | population | area |
+--------------+-------------+--------------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
+--------------+-------------+--------------+

MySQL脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- ----------------------------
-- Table structure for `world`
-- ----------------------------
DROP TABLE IF EXISTS `world`;
CREATE TABLE `world` (
`name`varchar(255) DEFAULT NULL,
`continent` varchar(255) DEFAULT NULL,
`area`int(11) DEFAULT NULL,
`population` int(11) DEFAULT NULL,
`gdp`varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of world
-- ----------------------------
INSERT INTO `world` VALUES ('Afghanistan','Asia', '652230', '25500100', '20343000000');
INSERT INTO `world` VALUES ('Albania','Europe', '28748', '2831741', '12960000000');
INSERT INTO `world` VALUES ('Algeria','Africa', '2381741', '37100000', '188681000000');
INSERT INTO `world` VALUES ('Andorra','Europe', '468', '78115', '3712000000');
INSERT INTO `world` VALUES ('Angola', 'Africa','1246700', '20609294', '100990000000');

本题回答

需要注意的是:如果一个国家的面积超过300万平方公里,或者人口超过2500万,那么这个国家就是大国家。

1
2
3
4
# Write your MySQL query statement below
SELECT name,population,area
FROM World
WHERE area > 3000000 OR population > 25000000;

596-超过5名学生的课

题目描述

有一个courses 表 ,有: student (学生) 和 class (课程)。

请列出所有超过或等于5名学生的课。

例如,表:

1
2
3
4
5
6
7
8
9
10
11
12
13
+---------+------------+
| student | class |
+---------+------------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
+---------+------------+

应该输出:

1
2
3
4
5
+---------+
| class |
+---------+
| Math |
+---------+

Note:学生在每个课中不应被重复计算。

MySQL脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- ----------------------------
-- Table structure for `courses`
-- ----------------------------
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses` (
`student` varchar(255) DEFAULT NULL,
`class`varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of courses
-- ----------------------------
INSERT INTO `courses` VALUES ('A', 'Math');
INSERT INTO `courses` VALUES ('B', 'English');
INSERT INTO `courses` VALUES ('C', 'Math');
INSERT INTO `courses` VALUES ('D', 'Biology');
INSERT INTO `courses` VALUES ('E', 'Math');
INSERT INTO `courses` VALUES ('F', 'Computer');
INSERT INTO `courses` VALUES ('G', 'Math');
INSERT INTO `courses` VALUES ('H', 'Math');
INSERT INTO `courses` VALUES ('I', 'Math');

本题回答

这表里有重复记录的,比如A学生选了Math的记录可以有多条,但是认为Math只被A学生选了1次

1
2
3
4
5
# Write your MySQL query statement below
SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;

601-体育馆的人流量

题目描述

X 市建了一个新的体育馆,每日人流量信息被记录在这三列信息中:序号 (id)、日期 (date)、 人流量 (people)。

请编写一个查询语句,找出高峰期时段,要求连续三天及以上,并且每天人流量均不少于100。

例如,表 stadium:

1
2
3
4
5
6
7
8
9
10
11
12
+------+------------+-----------+
| id | date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+

对于上面的示例数据,输出为:

1
2
3
4
5
6
7
8
+------+------------+-----------+
| id | date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+

Note:每天只有一行记录,日期随着 id 的增加而增加

MySQL脚本

1
2
3
4
5
6
7
8
9
10
Create table If Not Exists stadium (id int, visit_date DATE NULL, people int);
Truncate table stadium;
insert into stadium (id, visit_date, people) values ('1', '2017-01-01', '10');
insert into stadium (id, visit_date, people) values ('2', '2017-01-02', '109');
insert into stadium (id, visit_date, people) values ('3', '2017-01-03', '150');
insert into stadium (id, visit_date, people) values ('4', '2017-01-04', '99');
insert into stadium (id, visit_date, people) values ('5', '2017-01-05', '145');
insert into stadium (id, visit_date, people) values ('6', '2017-01-06', '1455');
insert into stadium (id, visit_date, people) values ('7', '2017-01-07', '199');
insert into stadium (id, visit_date, people) values ('8', '2017-01-08', '188');

本题回答

举例来说:(29,'2017-5-29',150),(30,'2017-6-1',150), 没有5月30日,按照日期检索就会直接跳过,但这个其实是算连续的,所以只能按照id来检索

1
2
3
4
5
6
7
8
9
10
11
12
SELECT DISTINCT t1.*
FROM stadium t1, stadium t2, stadium t3
WHERE t1.people >= 100 AND t2.people >= 100 AND t3.people >= 100
AND
(
(t1.id - t2.id = 1 AND t1.id - t3.id = 2 AND t2.id - t3.id = 1)
OR
(t2.id - t1.id = 1 AND t2.id - t3.id = 2 AND t1.id - t3.id = 1)
OR
(t3.id - t2.id = 1 AND t2.id - t1.id = 1 AND t3.id - t1.id = 2)
)
ORDER BY t1.id;

620-有趣的电影

题目描述

某城市开了一家新的电影院,吸引了很多人过来看电影。该电影院特别注意用户体验,专门有个 LED显示板做电影推荐,上面公布着影评和相关电影描述。

作为该电影院的信息部主管,您需要编写一个 SQL查询,找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片,结果请按等级 rating 排列。

例如,下表 cinema:

1
2
3
4
5
6
7
8
9
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card| Interesting| 9.1 |
+---------+-----------+--------------+-----------+

对于上面的例子,则正确的输出是为:

1
2
3
4
5
6
+---------+-----------+--------------+-----------+
| id | movie | description | rating |
+---------+-----------+--------------+-----------+
| 5 | House card| Interesting| 9.1 |
| 1 | War | great 3D | 8.9 |
+---------+-----------+--------------+-----------+

解题思路

MySQL判断奇偶数的方法

MySQL判断奇偶数的方法

排序

  • 查询结果可以用ORDER BY 排序;
1
SELECT * FROM tablename ORDER BY item
  • 默认按照升序排序(由小到大),也可变为降序(由大到小);
1
2
SELECT * FROM tablename ORDER BY item ASC     //升序(默认)
SELECT * FROM tablename ORDER BY item DESC //降序
  • 也可以用多个字段进行排序;
1
2
SELECT * FROM tablename ORDER BY item1 ASC,item2 DESC
//先按item1字段排序(升序),item1字段相等就按照item2字段排序(降序)。

本题回答

1
2
3
4
5
# Write your MySQL query statement below
SELECT id,movie,description,rating
FROM cinema
WHERE id REGEXP '[13579]$' AND description != 'boring'
ORDER BY rating DESC;

626-换座位

题目描述

小美是一所中学的信息科技老师,她有一张 seat 座位表,平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢?

示例:

1
2
3
4
5
6
7
8
9
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+

假如数据输入的是上表,则输出结果如下:

1
2
3
4
5
6
7
8
9
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+

注意:如果学生人数是奇数,则不需要改变最后一个同学的座位。

MySQL脚本

1
2
3
4
5
6
7
Create table If Not Exists seat(id int, studentvarchar(255));
Truncate table seat;
insert into seat (id, student) values ('1','Abbot');
insert into seat (id, student) values ('2','Doris');
insert into seat (id, student) values ('3','Emerson');
insert into seat (id, student) values ('4','Green');
insert into seat (id, student) values ('5','Jeames');

本题回答

我们可以分成三块来写,第一块就是id为偶数的,id-1就相当于和奇数的互换了,第二块是id为奇数的,id+1就相当于和偶数的互换了,最后一块是最后一个为奇数的,不换,然后三块合并排序就出来结果了。

方法1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# Write your MySQL query statement below
SELECT s.id,s.student
FROM (SELECT (id-1) AS id,student
FROM seat
WHERE mod(id,2) = 0
UNION
SELECT (id+1) AS id,student
FROM seat
WHERE mod(id,2) = 1 AND id != (SELECT COUNT(*) FROM seat)
UNION
SELECT id,student
FROM seat
WHERE mod(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat)) s
ORDER BY s.id;

需要注意的是,最后一块的过滤条件为WHERE mod(id,2) = 1 AND id = (SELECT COUNT(*) FROM seat,需要保证id不为偶数。

方法2——CASE WHEN

1
2
3
4
5
6
7
# Write your MySQL query statement below
SELECT (CASE
WHEN MOD(id,2)!=0 AND id!=counts THEN id+1
WHEN MOD(id,2)!=0 AND id=counts THEN id
ELSE id-1 END) AS id,student
FROM seat,(SELECT COUNT(*) AS counts FROM seat) AS seat_counts
ORDER BY id;

627-交换工资

题目描述

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

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

例如:

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

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

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

MySQL脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- ----------------------------
-- 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。

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

方法二:使用case…when..then..else..end

1
2
UPDATE salary 
SET sex = (CASE WHEN sex = 'm' THEN 'f' ELSE 'm' END);
赞赏一杯咖啡
0%