前言
能用单表优先用单表，即便是需要用group by、order by、limit等，效率一般也比多表高
不能用单表时优先用连接，连接是SQL中非常强大的用法，小表驱动大表+建立合适索引+合理运用连接条件，基本上连接可以解决绝大部分问题。但join级数不宜过多，毕竟是一个接近指数级增长的关联效果
能不用子查询、笛卡尔积尽量不用，虽然很多情况下MySQL优化器会将其优化成连接方式的执行过程，但效率仍然难以保证
自定义变量在复杂SQL实现中会很有用，例如LeetCode中困难级别的数据库题目很多都需要借助自定义变量实现
如果MySQL版本允许，某些带聚合功能的查询需求应用窗口函数是一个最优选择。除了经典的获取3种排名信息，还有聚合函数、向前向后取值、百分位等，具体可参考官方指南。以下是官方给出的几个窗口函数的介绍：
一、1951. 查询具有最多共同关注者的所有两两结对组
1.题目描述
表: Relations
+-------------+------+ | Column Name | Type | +-------------+------+ | user_id | int | | follower_id | int | +-------------+------+ (user_id, follower_id) 是这个表的主键. 这个表的每一行，表示这个user_id的用户和他的关注者，关注者的id 就是本表的 user_id.
写出一个查询语句，找到具有最多共同关注者的所有两两结对组。换句话说，如果有两个用户的共同关注者是最大的，我们应该返回所有具有此最大值的两两结对组
结果返回表，每一行应该包含user1_id和 user2_id，其中user1_id < user2_id.
返回结果 不要求顺序 。
查询结果格式如下例：
Relations 表: +---------+-------------+ | user_id | follower_id | +---------+-------------+ | 1 | 3 | | 2 | 3 | | 7 | 3 | | 1 | 4 | | 2 | 4 | | 7 | 4 | | 1 | 5 | | 2 | 6 | | 7 | 5 | +---------+-------------+ Result 表: +----------+----------+ | user1_id | user2_id | +----------+----------+ | 1 | 7 | +----------+----------+ 用户1 和用户 2 有2个共同的关注者（3和4）。 用户1 和用户 7 有3个共同的关注者（3，4和5）。 用户2 和用户7 有2个共同的关注者（3和4）。 既然两两结对的所有组队的最大共同关注者的数值是3，所以，我们应该返回所有拥有3个共同关注者的两两组队，这就是仅有的一对(1, 7). 我们返回的是(1, 7).，而不是(7, 1). 注意，我们没有关于用户3，4，5的任何关注者信息，我们认为他们有0个关注者。
2.解题思路
1）将Relations表和自己左连接，根据foller_id并通过where筛选出左小右大的形式，避免出现1，7和7，1的情况
2）用group by分组再使用排序窗口函数来根据关注者的数量排序
3）选取排序第一的即为结果
4）关键点利用排序窗口建立排序的子查询
3.代码实现
# Write your MySQL query statement below
SELECT
user1_id,
user2_id
FROM (
SELECT
r1.user_id AS user1_id,
r2.user_id AS user2_id,
RANK() OVER(ORDER BY COUNT(r1.follower_id) DESC) AS r
FROM Relations r1
LEFT JOIN Relations r2
ON r1.follower_id = r2.follower_id
WHERE r1.user_id < r2.user_id
GROUP BY r1.user_id, r2.user_id
) AS T
WHERE r = 1二、177. 第N高的薪水
1.题目描述
+-------------+------+ | Column Name | Type | +-------------+------+ | id | int | | salary | int | +-------------+------+ Id是该表的主键列。 该表的每一行都包含有关员工工资的信息。
编写一个SQL查询来报告 Employee 表中第 n 高的工资。如果没有第 n 个最高工资，查询应该报告为 null 。
查询结果格式如下所示。
示例 1:
输入: Employee table: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ n = 2 输出: +------------------------+ | getNthHighestSalary(2) | +------------------------+ | 200 | +------------------------+
示例 2:
输入: Employee 表: +----+--------+ | id | salary | +----+--------+ | 1 | 100 | +----+--------+ n = 2 输出: +------------------------+ | getNthHighestSalary(2) | +------------------------+ | null | +------------------------+
2.多种解题思路及代码实现
思路1：单表查询
由于本题不存在分组排序，只需返回全局第N高的一个，所以自然想到的想法是用order by排序加limit限制得到。需要注意两个细节：
1）同薪同名且不跳级的问题，解决办法是用group by按薪水分组后再order by
2）排名第N高意味着要跳过N-1个薪水，由于无法直接用limit N-1，所以需先在函数开头处理N为N=N-1。 注：这里不能直接用limit N-1是因为limit和offset字段后面只接受正整数（意味着0、负数、小数都不行）或者单一变量（意味着不能用表达式），也就是说想取一条，limit 2-1、limit 1.1这类的写法都是报错的。
注：这种解法形式最为简洁直观，但仅适用于查询全局排名问题，如果要求各分组的每个第N名，则该方法不适用；而且也不能处理存在重复值的情况。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N := N-1;
RETURN (
# Write your MySQL query statement below.
SELECT
salary
FROM
employee
GROUP BY
salary
ORDER BY
salary DESC
LIMIT N, 1
);
END
思路2：子查询
1）排名第N的薪水意味着该表中存在N-1个比其更高的薪水
2）注意这里的N-1个更高的薪水是指去重后的N-1个，实际对应人数可能不止N-1个
3）最后返回的薪水也应该去重，因为可能不止一个薪水排名第N
4）由于对于每个薪水的where条件都要执行一遍子查询，注定其效率低下
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT e.salary
FROM
employee e
WHERE
(SELECT count(DISTINCT salary) FROM employee WHERE salary>e.salary) = N-1
);
END
思路3：自连接
一般来说，能用子查询解决的问题也能用连接解决。具体到本题：
1）两表自连接，连接条件设定为表1的salary小于表2的salary
2）以表1的salary分组，统计表1中每个salary分组后对应表2中salary唯一值个数，即去重
3）限定步骤2中having 计数个数为N-1，即实现了该分组中表1salary排名为第N个
4）考虑N=1的特殊情形(特殊是因为N-1=0，计数要求为0)，此时不存在满足条件的记录数，但仍需返回结果，所以连接用left join
5）如果仅查询薪水这一项值，那么不用left join当然也是可以的，只需把连接条件放宽至小于等于、同时查询个数设置为N即可。因为连接条件含等号，所以一定不为空，用join即可。 注：个人认为无需考虑N<=0的情形，毕竟无实际意义。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
e1.salary
FROM
employee e1 JOIN employee e2 ON e1.salary <= e2.salary
GROUP BY
e1.salary
HAVING
count(DISTINCT e2.salary) = N
);
END
思路6：窗口函数
实际上，在mysql8.0中有相关的内置函数，而且考虑了各种排名问题：
row_number(): 同薪不同名，相当于行号，例如3000、2000、2000、1000排名后为1、2、3、4
rank(): 同薪同名，有跳级，例如3000、2000、2000、1000排名后为1、2、2、4
dense_rank(): 同薪同名，无跳级，例如3000、2000、2000、1000排名后为1、2、2、3
ntile(): 分桶排名，即首先按桶的个数分出第一二三桶，然后各桶内从1排名，实际不是很常用
显然，本题是要用第三个函数。 另外这三个函数必须要要与其搭档over()配套使用，over()中的参数常见的有两个，分别是
partition by，按某字段切分
order by，与常规order by用法一致，也区分ASC(默认)和DESC，因为排名总得有个依据
注：下面代码仅在mysql8.0以上版本可用，最新OJ已支持。
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
RETURN (
# Write your MySQL query statement below.
SELECT
DISTINCT salary
FROM
(SELECT
salary, dense_rank() over(ORDER BY salary DESC) AS rnk
FROM
employee) tmp
WHERE rnk = N
);
END
三、1596. 每位顾客最经常订购的商品
1.题目描述
表：Customers
+---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | name | varchar | +---------------+---------+ customer_id 是该表主键 该表包含所有顾客的信息
表：Orders
+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | order_date | date | | customer_id | int | | product_id | int | +---------------+---------+ order_id 是该表主键 该表包含顾客 customer_id 的订单信息 没有顾客会在一天内订购相同的商品 多于一次
表：Products
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | product_name | varchar | | price | int | +---------------+---------+ product_id 是该表主键 该表包含了所有商品的信息
写一个 SQL 语句，找到每一个顾客最经常订购的商品。
结果表单应该有每一位至少下过一次单的顾客 customer_id , 他最经常订购的商品的 product_id 和 product_name。
返回结果 没有顺序要求。
查询结果格式如下例所示。
示例 1：
输入： Customers表：+-------------+-------+ | customer_id | name | +-------------+-------+ | 1 | Alice | | 2 | Bob | | 3 | Tom | | 4 | Jerry | | 5 | John | +-------------+-------+Orders表：+----------+------------+-------------+------------+ | order_id | order_date | customer_id | product_id | +----------+------------+-------------+------------+ | 1 | 2020-07-31 | 1 | 1 | | 2 | 2020-07-30 | 2 | 2 | | 3 | 2020-08-29 | 3 | 3 | | 4 | 2020-07-29 | 4 | 1 | | 5 | 2020-06-10 | 1 | 2 | | 6 | 2020-08-01 | 2 | 1 | | 7 | 2020-08-01 | 3 | 3 | | 8 | 2020-08-03 | 1 | 2 | | 9 | 2020-08-07 | 2 | 3 | | 10 | 2020-07-15 | 1 | 2 | +----------+------------+-------------+------------+Products表：+------------+--------------+-------+ | product_id | product_name | price | +------------+--------------+-------+ | 1 | keyboard | 120 | | 2 | mouse | 80 | | 3 | screen | 600 | | 4 | hard disk | 450 | +------------+--------------+-------+ 输出： +-------------+------------+--------------+ | customer_id | product_id | product_name | +-------------+------------+--------------+ | 1 | 2 | mouse | | 2 | 1 | keyboard | | 2 | 2 | mouse | | 2 | 3 | screen | | 3 | 3 | screen | | 4 | 1 | keyboard | +-------------+------------+--------------+ 解释： Alice (customer 1) 三次订购鼠标, 一次订购键盘, 所以鼠标是 Alice 最经常订购的商品. Bob (customer 2) 一次订购键盘, 一次订购鼠标, 一次订购显示器, 所以这些都是 Bob 最经常订购的商品. Tom (customer 3) 只两次订购显示器, 所以显示器是 Tom 最经常订购的商品. Jerry (customer 4) 只一次订购键盘, 所以键盘是 Jerry 最经常订购的商品. John (customer 5) 没有订购过商品, 所以我们并没有把 John 包含在结果表中.
2.解题思路
此题关键表是Orders表，因此我们先根据顾客产品序号分组，其次使用排序窗口函数来排序订购次数由大到小建立子查询，注意这里CUOUNT最好用主键order_id速度会快一些。
子查询连接有产品名字的表，最后根据排序第一高的筛选出结果
3.代码实现
# Write your MySQL query statement below
SELECT
t.customer_id,
t.product_id,
p.product_name
FROM (
SELECT
customer_id ,
product_id,
RANK() OVER(PARTITION BY customer_id ORDER BY COUNT(order_id) DESC) AS r
FROM Orders
GROUP BY customer_id, product_id
) AS t
LEFT JOIN Products p
ON t.product_id = p.product_id
WHERE t.r = 1四、1285. 找到连续区间的开始和结束数字
1.题目描述
表：Logs
+---------------+---------+ | Column Name | Type | +---------------+---------+ | log_id | int | +---------------+---------+ id 是上表的主键。 上表的每一行包含日志表中的一个 ID。
后来一些 ID 从 Logs 表中删除。编写一个 SQL 查询得到 Logs 表中的连续区间的开始数字和结束数字。
将查询表按照 start_id 排序。
查询结果格式如下面的例子。
2.解题思路
连续问题的常见套路：使用真实值 - 出现次数是一个定值 详情可见窗口函数习题的第一弹，因此第一想到ROW_NUMBER窗口函数排序，本题无需分组，logid即为真实值，直接排序就是出现次数，如果相减定值说明连续 以相减列分组进行group by
注意的是起始和结束只要找到分组后id的最大最小值即可
3.代码实现
# Write your MySQL query statement below
SELECT
MIN(log_id) AS start_id,
MAX(log_id) AS end_id
FROM (
SELECT
log_id,
(log_id - ROW_NUMBER() OVER(ORDER BY log_id )) AS r
FROM Logs
) AS t
GROUP BY r
ORDER BY start_id
2681
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
