前言

实验环境

  • 英文题面
  • docker-machine with virtualbox drive
    • mysql 8.0.19

MYSQL Tips

  • CRTL+L:清屏

常用网址

175. Combine Two Tables

分类:数据库
难度:简单

一开始看到俩 PersonId 就直接内连接查询,用样例数据完全没有输出。仔细看题才发现两个表的主键不同,因此 Address 表中的 PersonId 有可能为空或匹配不到,而此时仍然需要输出人名。

使用 LEFT JOIN 保留 Person 表中的数据,当 PersonId 匹配失败时输出 FirstNameLastName,其余字段为 NULL

1
2
3
4
SELECT FirstName, LastName, City, State
FROM Person
LEFT JOIN Address
ON Person.PersonId = Address.PersonId;

176. Second Highest Salary

分类:数据库
难度:简单

首先把选择第二高薪资的查询语句写出来,即括号内语句;其次在外嵌套一个 SELECT 语句,以实现没有找到时输出 NULL

需要加上 DISTINCT 去重xd

1
2
3
4
5
6
SELECT(
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1, 1
)AS SecondHighestSalary;

另一种解法,从提交反馈看来相对更快的写法是使用 ISNULL

1
2
3
4
5
6
7
8
SELECT
IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1,1),
NULL)
AS SecondHighestSalary;

181. Employees Earning More Than Their Managers

分类:数据库
难度:简单

表的内连接加上条件即可

1
2
3
4
5
6
SELECT e1.Name
AS Employee
FROM Employee e1
JOIN Employee e2
ON e1.ManagerId = e2.Id
AND e1.Salary > e2.Salary;

另一种解法,相对更快

1
2
3
4
SELECT *
FROM Employee e1, Employee e2
WHERE e1.ManagerId = e2.Id
AND e1.Salary > e2.Salary;

182. Duplicate Emails

分类:数据库
难度:简单

分组计数,满足条件的输出。

1
2
3
4
SELECT Email
FROM Person
GROUP BY Email
HAVING COUNT(Email) > 1;

183. Customers Who Never Order

分类:数据库
难度:简单

第一反应就是用连接保留 Customers 表的数据,再按题意将没有订单记录的用户输出。

表连接的结果如下:

Id Name Id CustomerId
3 Sam 1 3
1 Joe 2 1
2 Henry NULL NULL
4 Max NULL NULL
1
2
3
4
5
6
SELECT Name
AS Customers
FROM Customers
LEFT JOIN Orders
ON Customers.Id = Orders.CustomerId
WHERE Orders.Id IS NULL;

另一种相对更快的解法(表连接,真的慢)

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

196. Delete Duplicate Emails

分类:数据库
难度:简单

连接之后按条件删除。

1
2
3
4
5
DELETE p1
FROM Person p1
JOIN Person p2
WHERE p1.id > p2.id
AND p1.Email = p2.Email;

另一种解法,相对更快一点。

1
2
3
4
5
6
7
8
9
10
11
DELETE
FROM Person
WHERE Id NOT IN(
SELECT Id
FROM(
SELECT MIN(Id)
AS Id
FROM Person
GROUP BY Email
)AS tmp -- Every derived table must have its own alias
);

197. Rising Temperature

分类:数据库
难度:简单

RecordDate 字段进行条件连接,然后比较 Temperature 的大小再输出 Id 即可。

1
2
3
4
5
SELECT w1.Id
FROM Weather w1
JOIN Weather w2
ON w1.RecordDate = DATE_ADD(w2.RecordDate, INTERVAL 1 DAY)
WHERE w1.Temperature > w2.Temperature; -- 也可以使用 AND

使用用 DATEDIFF 的解法,相对更快一点。

1
2
3
4
5
SELECT w1.Id
FROM Weather w1
JOIN Weather w2
ON DATEDIFF(w1.RecordDate, w2.RecordDate) = 1
WHERE w1.Temperature > w2.Temperature; -- 也可以使用 AND

595. Big Countries

分类:数据库
难度:简单

gdp 字段应该用 bigint 否则默认情况下 mysql 的 int 是存不了这些数据的。

满足条件输出即可

1
2
3
4
SELECT name, population, area
FROM World
WHERE area > 3000000
OR population > 25000000;

或者使用 UNION 合并两个查询结果再输出

1
2
3
4
5
6
7
SELECT name, population, area
FROM World
WHERE area > 3000000
UNION
SELECT name, population, area
FROM World
WHERE population > 25000000;

596. Classes More Than 5 Students

分类:数据库
难度:简单

分组计数输出满足条件的课程名称

1
2
3
4
SELECT class
FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;

或者先建立临时表储存 课程-人数 ,然后再输出参与人数大于等于 5 的课程,执行速度相对来说更快。

1
2
3
4
5
6
7
SELECT class
FROM(
SELECT class, COUNT(DISTINCT student) AS num
FROM courses
GROUP BY class
) AS tmp
WHERE num >= 5;

620. Not Boring Movies

分类:数据库
难度:简单

选择 id 为奇数,description 不是 boring 的数据,并将其降序排列。

1
2
3
4
5
SELECT *
FROM cinema
WHERE (id % 2) > 0
AND description NOT LIKE '%boring%'
ORDER BY rating DESC;

627. Swap Salary

分类:数据库
难度:简单

要求不要使用查询语句

1
2
3
4
5
UPDATE salary
SET sex = CASE
WHEN sex = 'm' THEN 'f'
WHEN sex = 'f' THEN 'm'
ELSE sex END;

也可以简化为一条分支语句

1
2
3
4
5
6
7
8
UPDATE salary
SET sex = CASE
WHEN sex = 'm' THEN 'f'
ELSE 'm' END;

-- 使用 IF 的方法
UPDATE salary
SET sex = IF(sex='f', 'm', 'f');

1179. Reformat Department Table

分类:数据库
难度:简单

id 分组,把 12 个月的收入按月求和输出,并重命名。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT id,
SUM(IF(month='Jan', revenue, NULL)) AS Jan_Revenue,
SUM(IF(month='Feb', revenue, NULL)) AS Feb_Revenue,
SUM(IF(month='Mar', revenue, NULL)) AS Mar_Revenue,
SUM(IF(month='Apr', revenue, NULL)) AS Apr_Revenue,
SUM(IF(month='May', revenue, NULL)) AS May_Revenue,
SUM(IF(month='Jun', revenue, NULL)) AS Jun_Revenue,
SUM(IF(month='Jul', revenue, NULL)) AS Jul_Revenue,
SUM(IF(month='Aug', revenue, NULL)) AS Aug_Revenue,
SUM(IF(month='Sep', revenue, NULL)) AS Sep_Revenue,
SUM(IF(month='Oct', revenue, NULL)) AS Oct_Revenue,
SUM(IF(month='Nov', revenue, NULL)) AS Nov_Revenue,
SUM(IF(month='Dec', revenue, NULL)) AS Dec_Revenue
FROM Department
GROUP BY id;

也可以使用 CASE 语句。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT id,
SUM(CASE month WHEN 'Jan' THEN revenue END) AS Jan_Revenue,
SUM(CASE month WHEN 'Feb' THEN revenue END) AS Feb_Revenue,
SUM(CASE month WHEN 'Mar' THEN revenue END) AS Mar_Revenue,
SUM(CASE month WHEN 'Apr' THEN revenue END) AS Apr_Revenue,
SUM(CASE month WHEN 'May' THEN revenue END) AS May_Revenue,
SUM(CASE month WHEN 'Jun' THEN revenue END) AS Jun_Revenue,
SUM(CASE month WHEN 'Jul' THEN revenue END) AS Jul_Revenue,
SUM(CASE month WHEN 'Aug' THEN revenue END) AS Aug_Revenue,
SUM(CASE month WHEN 'Sep' THEN revenue END) AS Sep_Revenue,
SUM(CASE month WHEN 'Oct' THEN revenue END) AS Oct_Revenue,
SUM(CASE month WHEN 'Nov' THEN revenue END) AS Nov_Revenue,
SUM(CASE month WHEN 'Dec' THEN revenue END) AS Dec_Revenue
FROM Department
GROUP BY id;