Leetcode数据库刷题笔记

一、简单题

176 排序

题解：

SELECT IFNULL(
    (SELECT DISTINCT salary
    FROM employee
    ORDER BY salary DESC
    LIMIT 1 OFFSET 1), NULL)
AS SecondHighestSalary

分析：本题有三个知识点：

1. DISTINCT字段的使用：由于工资可能存在重复，所以需要只返回不同的值
2. LIMIT OFFSET字段的使用：第一个参数表示返回记录行的偏移量，注意初始记录行的偏移量为0而非1，第二个参数表示返回记录行的最大数目。
3. IFNULL函数的使用：该函数有两个参数，当第一个参数为非NULL时返回值，否则返回NULL。

此外本题还可以使用窗口函数解决。
窗口函数的基本形式：

<窗口函数> over (partition by <分组> order by <排序>)

<窗口函数>中可以使用的函数类型有：

1. 专用窗口函数：包括rank, dense_rank, row_number等
2. 聚合函数：包括sum, avg, count, max, min等

窗口函数使用顺序在WHERE GROUP BY子句之后，只能使用在SELECT子句中。

182 查找重复的电子邮箱

题解：

SELECT Email
FROM person
GROUP BY Email
HAVING COUNT(id) > 1

分析：本题涉及到聚合函数，聚合函数有以下特点：

1. 统计结果中默认忽略字段为NULL的记录
2. 如果要使得NULL字段也参与计算，需要利用IFNULL函数进行转换
3. 不能出现嵌套

常见的聚合函数：
COUNT用于计数，AVG, SUM, MAX, MIN。
一般聚合函数会和分组函数一起使用，对组进行计算。

SELECT COUNT(*)
FROM table1
GROUP BY col1

注意分组后不能再用WHERE进行筛选，而应用HAVING。

183 从不订购的用户

题解1，使用外联结：

SELECT DISTINCT name Customers
FROM customers c LEFT JOIN orders o
ON c.id = o.customerid
WHERE o.id IS NULL

这种思路比较直接，利用了外联结的特性，但是效率可能不是很高

题解2，利用子查询：

SELECT Name AS Customers
FROM Customers
WHERE id NOT IN(
    SELECT DISTINCT CustomerID
    FROM orders
)

这里先查询出所有存在订单的客户的ID，作为子查询的临时表，然后利用NOT IN进行筛选。

196 删除重复的电子邮箱

题解1：

DELETE FROM person
WHERE id NOT IN(
    SELECT t.min_id
    FROM (
        SELECT MIN(id) min_id
        FROM person
        GROUP BY email
    ) t
)

分析：该方法主要涉及到DELETE方法
注意这里涉及到一个常见错误：

## You can't specify target table '表名' for update in FROM clause

就是说在MySQL中，不能根据某一张表中查询得到的值再更新同一张表。此时需要再创建一个临时表，比如本题中创建了临时表t，这样就可以避免问题发生。

题解2，多表删除方法：

DELETE p1
FROM person p1 JOIN person p2
ON p1.email = p2.email
AND p1.id > p2.id

注意DELETE语句中后面跟的表名即为需要删除记录的表。
既然提到了多表删除，那不妨再复习一下多表连接：

1. 内连接：INNER JOIN或JOIN，获取两个表中字段匹配关系的记录
2. 左连接：LEFT JOIN，获取左表的所有记录，即便右表中没有对应匹配的记录
3. 自然连接：通过MySQL根据多个表内的相同字段作为连接条件，自动完成连接过程的连接方式。

197 上升的温度

题解1，两表联结：

SELECT w1.Id
FROM weather w1 JOIN weather w2
ON DATEDIFF(w1.recorddate, w2.recorddate) = 1
AND w1.temperature > w2.temperature

分析：本题涉及到的主要知识点为DATEDIFF函数。
DATEDIFF(日期1，日期2）是用日期1减去日期2。
除了DATEDIFF函数以外，还有功能更为强大的TIMESTAMPDIFF函数：
TIMESTAMPDIFF(unit, time1, time2)，注意这里的差别是用time2 - time1，unit的常用选项有：
YEAR，MONTH，WEEK,DAY,HOUR,MINUTE

题解2，变量法：

SELECT ID
FROM(
	SELECT w.*,
		@curd := w.recorddate,
		@curt := w.temperature,
		@isr := IF(DATEDIFF(@curd, @pred) = 1 AND @curt > @pret, 1, 0) r,
		@pred := @curd,
		@pret := @curt
	FROM weather w,
	（SELECT 
		@curd := NULL, #存储当前日期
		@pred := NULL, #存储上一天的日期
		@curt := 0,	#存储当前温度
		@pred := 0,	#存储上一天温度
		@isR := 0	#判断是否满足条件
	)t
	ORDER BY w.recorddate
) temp
WHERE temp.r = 1
WHERE

变量法相对比较复杂，但是处理效率较高，不需要考虑表的连接，在后面处理困难问题中是常用的方法。

620 有趣的电影

题解：

SELECT *
FROM cinema
WHERE id % 2 = 1 AND description != 'boring'

分析：本题比较简单，主要关注两个知识点：

1. %运算，表示取余，当然也可以用MOD(num1, num2)表示数1对数2取余后的结果
2. != 判别符，表示不等

627 交换工资

题解1，用CASE WHEN函数：

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

题解2，用IF函数：

UPDATE salary
SET sex = IF(sex = 'm', 'f', 'm')

分析：本题涉及以下知识点：

1. 更新表。更新表的sql基本语法为：UPDATE table SET col1 = new-value, col2 = new-value WHERE clause
2. IF函数：IF(condition, value_if_true, value_if_false)
3. CASE WHEN函数：sql中的CASE函数有两种用法。一种是简单函数，另一种是搜索函数，示例如下：

-- 简单函数
CASE SEX
WHEN '1' THEN '男'
WHEN '0' THEN '女'
ELSE 'unknow'
END

-- 搜索函数
CASE
WHEN sex = '1' AND age < 10 THEN '男孩'
WHEN sex = '0' AND age < 10 THEN '女孩'
ELSE 'unknow'
END

1179 重新格式化部门表

编写一个SQL查询重新格式化表，使得新表中有一个部门id列和对应每个月的收入列

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

分析：本题涉及到行转列的方法，需要注意。

二、中等题

177 第N高的薪水

题解

CREATE FUNCTION getNthHighestSalary(N INT）
RETURNS INT
BEGIN
	DECLARE p INT DEFAULT N - 1;
	IF p < 0 THEN RETURN NULL;
	ELSE RETURN(
		SELECT DISTINCT salary
		FROM employee
		ORDER BY salary DESC
		LIMIT p OFFSET 1);
	END IF;
END

分析：本题的重点在于自定义函数。
注意MySQL中函数只能返回一个值，不能返回一整个查询的结果集。
函数创建的通用语法：

CREATE FUNCTION 函数名([参数列表])
RETURNS 数据类型
BEGIN
	sql语句;
	RETURN 值;
END

创建函数示例：

CREATE FUNCTION myselect(name VARCHAR(15))
RETURNS INT
BEGIN
	DECLARE c INT;
	SELECT id 
	FROM dataset
	WHERE cname = name
	INTO c;
	RETURN c;
END

调用函数示例：

SELECT myselect("python");

178 分数排名

题解1，窗口函数：

SELECT score,
	dense_rank() OVER (ORDER BY score DESC) Rank
FROM scores

本题如果使用窗口函数解决会非常简便

题解2：

SELECT s1.Score, COUNT(DISTINCT s2.id) Rank
FROM scores s1 JOIN scores s2
WHERE s1.score <= s2.score
GROUP BY s1.id
ORDER BY s1.score DESC

本题用到了多表连接查询个数的方法。

180 连续出现的数字

题解1，多表连接：

SELECT DISTINCT l1.num ConsecutiveNums
FROM logs l1 JOIN logs l2
ON l1.id >= l2.id AND l1.id <= l2.id + 2
	AND l1.num = l2.num
GROUP BY l1.id
HAVING COUNT(l2.id) >= 3

题解2，变量法：

SELECT DISTINCT temp.num ConsecutiveNums
FROM(
SELECT num,
	case
	when @prev = num then @count := @count + 1
	when (@prev := num) then @count := 1
	end cnt
FROM logs,
	(SELECT @prev := null,
		@count := null) t
		) temp
WHERE temp.cnt >= 3

184 部门工资最高的员工

题解1，用窗口函数：

SELECT t.Department, t.name Employee, t.Salary
FROM
(SELECT e.*, DENSE_RANK() OVER(PARTITION BY id ORDER BY salary DESC) max_salary, d.name Department
FROM employee e JOIN department d
ON e.departmentid = d.id) t
WHERE t.max_salary = 1

本题用窗口函数解决还是比较简单的，主要是需要注意窗口函数的使用方法，这里用DENSE_RANK()函数而不是RANK()函数，此外还要注意表的联结。
题解2，用IN字段匹配查询：

SELECT d.name Department, e.Name Employee, e.Salary
FROM department d JOIN employee e
ON d.id = e.departmentid
WHERE (e.name, e.salary) IN (
	SELECT name, MAX(salary)
	FROM employee
	GROUP BY name)

分析：
首先注意IN的使用对象，它用于匹配某个查询结果集，
此外注意IN的使用方法拓展，它也可以用于二元匹配值，用括号括起来就可以。

626 换座位

SELECT student,
	CASE
		WHEN MOD(id, 2) = 0 THEN id - 1
		WHEN MOD(id, 2) = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
		ELSE id + 1
	END id
FROM seat
ORDER BY id