前言

实验环境

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

MYSQL Tips

  • CRTL+L：清屏

常用网址

175. Combine Two Tables

分类：数据库
难度：简单

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

使用 LEFT JOIN 保留 Person 表中的数据，当 PersonId 匹配失败时输出 FirstName 和 LastName，其余字段为 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;