Leetcode Database 刷题

175. 组合两个表

左连接

SELECT
	p.FirstName,
	p.LastName,
	a.City,
	a.State
FROM
	Person p LEFT JOIN Address a
ON
	p.PersonId = a.PersonId

176. 第二高的薪水

子查询

ifnull() 函数

SELECT
	ifnull(
		(
			SELECT DISTINCT
				Salary
			FROM
				Employee
			ORDER BY
				Salary
			DESC
			LIMIT 1, 1
		),
		NULL
	)
AS SecondHighestSalary

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

177. 第N高的薪水

178. 分数排名

180. 连续出现的数字

自连接

如果一个数字连续出现超过 3 次，会返回重复元素，需要使用 DISTINCT 去重。

SELECT DISTINCT
	l1.Num AS ConsecutiveNums
FROM
	`Logs` l1,
	`Logs` l2,
	`Logs` l3
WHERE
	l1.Num = l2.Num AND l2.Num = l3.Num AND l1.Id = l2.Id - 1 AND l2.Id = l3.Id - 1

181. 超过经理收入的员工

SELECT
	e1.NAME AS Employee
FROM
	Employee e1,
	Employee e2
WHERE
	e1.ManagerId = e2.Id
	AND e1.Salary > e2.Salary

182. 查找重复的电子邮箱

分组聚合

SELECT
	Email
FROM
	Person
GROUP BY
	Email
HAVING
	count(Email) > 1

183. 从不订购的客户

1. 左连接

SELECT
	NAME AS 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
	Customers.Id NOT IN (SELECT CustomerId FROM Orders)

184. 部门工资最高的员工

1. 内连接 + 范围查询

SELECT
	d.NAME AS Department,
	e.NAME AS Employee,
	e.Salary
FROM
	Employee e,
	Department d
WHERE
	e.DepartmentId = d.Id
	AND (e.DepartmentId, Salary) IN (
		SELECT
			DepartmentId,
			MAX(Salary)
		FROM
			Employee
		GROUP BY
			DepartmentId
	)

2. 内连接 + 子查询

SELECT
	d.NAME AS Department,
	e.NAME AS Employee,
	e.Salary
FROM
	Employee e,
	Department d,
	(
		SELECT
			DepartmentId,
			MAX(Salary) AS salary
		FROM
			Employee
		GROUP BY
			DepartmentId
	) AS t
WHERE
	e.DepartmentId = d.Id
	AND e.DepartmentId = t.DepartmentId
	AND e.Salary = t.Salary

185. 部门工资前三高的所有员工

1. 重复薪水的处理

   员工的薪水可能相同，前三高工资的记录数可能大于 3，使用 ORDER BY Salary DESC LIMIT 3 无法满足题目要求。

   并且，本题中相同工资视为同一排名，不会挤占后面的排位。例如 [200, 150, 150, 100, 90]，前三高为 [200, 150, 150, 100]，而不是 [200, 150, 150]。因此，使用 COUNT(DISTINCT e2.Salary) 去除重复工资的计数。

2. 子查询

   将子查询结果作为筛选条件，产生类似遍历的效果。在本题中，对于员工表中的每一条记录，通过子查询查找出同一部门中工资高于该记录的员工数，从而判断该记录是否符合前三高条件，完成条件筛选。

3. 分组聚合

   一般地，聚合函数与 GROUP BY、HAVING 配合使用。聚合函数作为筛选条件时，只能出现在 HAVING 语句中。聚合函数作为查询结果时，可以单独出现在 SELECT 语句中，此时默认分组是整个表。

   本题中，筛选条件是员工数量，而又不便于划分分组，不得不采用子查询的写法。

4. 连接的条件筛选

   ON 用于在生成临时表时的条件筛选；WHERE 用于对已生成的临时表进行条件筛选。

   以左连接为例，不论 ON 中的条件是否为真，都会返回左表中的记录，即 ON 条件对左表无效。而 WHERE 作用于临时表，对来自各表中的字段一视同仁。

   对于内连接，ON 要求各表都满足筛选条件，原理上与 WHERE 作用对象不同，效果上却是完全相同。因此，内连接中 ON 可以替代 WHERE，反之不行。实践中，一般使用 ON …… AND …… 而很少用到 ON …… WHERE ……。

SELECT
	d.NAME AS Department,
	e1.NAME AS Employee,
	e1.Salary
FROM
	Employee e1 JOIN Department d
ON
	e1.DepartmentId = d.Id
AND
	3 > (
		SELECT
			COUNT(DISTINCT e2.Salary)
		FROM
			Employee e2
		WHERE
			e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId
	)

196. 删除重复的电子邮箱（自连接、删除语句）

自连接

多表连接删除：将需要执行操作的表名写在 DELETE 后面。这样，删除只影响部分表，但同时利用多张表进行查询。

DELETE
    p1
FROM
	Person p1,
	Person p2
WHERE
	p1.Email = p2.Email AND p1.Id > p2.Id

197. 上升的温度

DATEDIFF() 用于计算两个日期值相差的天数，数据类型可以是 DATE、DATETIME 和 TIMESTAMP，对于 DATETIME 或 TIMESTAMP，该函数仅将日期部分用于计算，并忽略时间部分。

ADDDATE(date, INTERVAL 1 DAY) 对当前日期增加一定量的时间。

SELECT
	w2.Id
FROM
	Weather w1,
	Weather w2
WHERE
	ADDDATE(w1.RecordDate, INTERVAL 1 DAY) = w2.RecordDate AND w1.Temperature < w2.Temperature

SELECT
	w1.Id AS Id
FROM
	Weather w1,
	Weather w2
WHERE
	DATEDIFF(w1.RecordDate, w2.RecordDate) = 1 AND w1.Temperature > w2.Temperature

262. 行程和用户

595. 大的国家

596. 超过5名学生的课

分组聚合

陷阱：表中存在重复记录，在分组计数时应该去重。例如，

student	class
A	Math
B	English
C	Math
D	Biology
E	Math
F	Math
A	Math

SELECT
	class
FROM
	courses
GROUP BY
	class
HAVING
	count(DISTINCT student) >= 5

601. 育馆的人流量

620. 有趣的电影

MySQL 常用运算符及函数汇总：https://www.jb51.net/article/124096.htm

SELECT
	*
FROM
	cinema
WHERE
	description <> 'boring' AND id % 2 = 1
ORDER BY
	rating
DESC

626. 换座位

条件语句 IF(condition, true_operation, false_operationg) 和 CASE……WHEN……THEN……ELSE……THEN……END

聚合函数作为筛选条件时，只能出现在 HAVING 语句中。没有使用 GROUP BY 创建分组，不能直接使用 id=MAX(id) 筛选最大 ID，否则报 Invalid use of group function 错误，只能先通过子查询计算最大值，再进行条件筛选。

SELECT
	CASE
		WHEN id % 2 = 0
			THEN id - 1
		WHEN id = (SELECT MAX(id) FROM seat)
			THEN id
		ELSE id + 1
	END AS id, student
FROM
	seat
ORDER BY
	id

SELECT
	IF (
		id % 2 = 0,
		id - 1,
		IF (
			id = (SELECT MAX(id) FROM seat),
			id,
			id + 1
		)
	) AS id, student
FROM
	seat
ORDER BY
	id

627. 交换工资

1. 条件语句

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

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

2. 异或/加减交换

UPDATE
	salary
SET
	sex = CHAR (ASCII(sex) ^ ASCII('m') ^ ASCII('f'))

UPDATE
	salary
SET
	sex = CHAR (ASCII('m') + ASCII('f') - ASCII(sex))

题目列表：

• Leetcode 175. 组合两个表
• Leetcode 176. 第二高的薪水
• Leetcode 177. 第N高的薪水
• Leetcode 178. 分数排名
• Leetcode 180. 连续出现的数字
• Leetcode 181. 超过经理收入的员工
• Leetcode 182. 查找重复的电子邮箱
• Leetcode 183. 从不订购的客户
• Leetcode 184. 部门工资最高的员工
• Leetcode 185. 部门工资前三高的所有员工
• Leetcode 196. 删除重复的电子邮箱（自连接、删除语句）
• Leetcode 197. 上升的温度
• Leetcode 262. 行程和用户
• Leetcode 595. 大的国家
• Leetcode 596. 超过5名学生的课
• Leetcode 601. 体育馆的人流量
• Leetcode 620. 有趣的电影
• Leetcode 626. 换座位
• Leetcode 627. 交换工资