sqlleftjoin如果为空不关联_一日一技：一句SQL，我有6种写法

导读

最近在刷LeetCode中数据库题目时&＃xff0c;有一道排名题目&＃xff0c;用了6种写法分别代表6种SQL思维来实现&＃xff0c;想想也算是有趣。

文章来源&＃xff1a;小数志

作者&＃xff1a;luanhz

题目描述&＃xff1a;

题意理解不难&＃xff0c;无非就是查找排名为N的记录&＃xff0c;但常用SQL的都知道这里存在一个歧义&＃xff0c;即排名是否存在相同和是否跳级的问题。经测试&＃xff0c;这里的排名是"致密"排名(dense_rank)&＃xff0c;即同薪同名且不跳级那种。例如对于薪水3000/2000/2000/1000排名之后为1、2、2、3&＃xff0c;若取N&＃61;3&＃xff0c;则返回结果1000。另外&＃xff0c;题目形式是一个自定义函数&＃xff0c;但本质仍是一个SQL查询。

面对这样的一道题&＃xff0c;你能迅速想到几种SQL写法呢&＃xff1f;

---

解法1 limit&＃43;offset由于这里题目需求很简单&＃xff0c;仅仅是返回全局的第N高薪水&＃xff0c;而不存在分组排名或其他需求&＃xff0c;所以最简单的办法就是用limit&＃43;offset关键字直接获取。SQL语句&＃xff1a;

1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3  SET N &＃61; N - 1;
4  RETURN (
5      SELECT 
6            salary
7      FROM 
8            employee
9      GROUP BY 
10            salary
11      ORDER BY 
12            salary DESC
13      LIMIT 1 OFFSET N
14  );
15END
执行效率&＃xff1a;

由于只进行单表查询&＃43;单字段排序&＃xff0c;对salary字段建立索引时查询效率会非常高。

---

解法2 子查询

既然是排名为N&＃xff0c;那么就意味着大于等于目标薪水的记录数为N&＃xff0c;更准确的说这里是去重后的记录数为N。基于此想法&＃xff0c;很快可以写出相应SQL&＃xff1a;

SQL语句&＃xff1a;

1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3  RETURN (
4      SELECT 
5          DISTINCT e.salary
6      FROM 
7          employee e
8      WHERE 
9          (SELECT count(DISTINCT salary) FROM employee WHERE salary>&＃61;e.salary) &＃61; N
10  );
11END
执行效率&＃xff1a;

这个子查询效率要低不少&＃xff0c;因为每条记录都要执行一条子查询判断聚合次数是否等于N。

---

解法3 连接查询

个人认为&＃xff0c;SQL最强大也最有代表性的操作在于多表关联&＃xff0c;这个问题自然也可以用连接查询。MySQL中主要支持join、left join和right join三种连接方式。具体到这一题&＃xff0c;可以选用任何一种。例如&＃xff0c;如果限定连接条件是薪水大于等于(含等于)&＃xff0c;则可直接用join实现两表自连接&＃xff0c;然后对另一个计数即可&＃xff1b;而如果限定连接条件是薪水大于(不含等于)&＃xff0c;则必须用left join&＃xff0c;避免N取特殊值1时出现关联结果为空而查询失败的情况。具体来说&＃xff1a;

应用join的SQL语句&＃xff1a;

1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3  RETURN (
4      SELECT 
5          DISTINCT e1.salary
6      FROM 
7          employee e1 JOIN employee e2 ON e1.salary <&＃61; e2.salary
8      GROUP BY 
9          e1.salary
10      HAVING 
11          count(DISTINCT e2.salary) &＃61; N
12  );
13END
执行效率&＃xff1a;

应用left join的SQL语句&＃xff1a;

1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3  RETURN (
4      SELECT 
5          DISTINCT e1.salary
6      FROM 
7          employee e1 LEFT JOIN employee e2 ON e1.salary  8      GROUP BY 
9          e1.salary
10      HAVING 
11          count(DISTINCT e2.salary) &＃61; N-1
12  );
13END
另外&＃xff0c;right join本质上和left join是一致的&＃xff0c;简单交换两表顺序可以很容实现right join写法。执行效率&＃xff1a;

可见&＃xff0c;无论是用内连接还是外连接&＃xff0c;效率都不是太高&＃xff0c;与子查询效率相当。

---

解法4 笛卡尔积

用join连接方式实现的SQL&＃xff0c;都能用笛卡尔积实现&＃xff0c;且一般来说笛卡尔效率要略低于连接查询&＃xff0c;但很多情况下MySQL优化器会将笛卡尔积形式的查询优化成join形式&＃xff0c;此时二者执行过程是一致的。可以很容易将解法3中的形式改成笛卡尔积形式的写法。

SQL语句&＃xff1a;

1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3  RETURN (
4      SELECT 
5          DISTINCT e1.salary
6      FROM 
7          employee e1, employee e2 
8      WHERE 
9          e1.salary <&＃61; e2.salary
10      GROUP BY 
11          e1.salary
12      HAVING 
13          count(DISTINCT e2.salary) &＃61; N
14  );
15END
执行效率&＃xff1a;

这个查询的效率相比连接查询和子查询又要略低一些。

---

解法5 自定义变量

前面已经介绍了4种解法&＃xff0c;对比来看&＃xff1a;解法2-4中都存在两表关联的问题&＃xff0c;而解法1因为仅涉及到单表排序&＃xff0c;所以效率相比之下更高&＃xff1b;另一方面&＃xff0c;解法2-4功能更具扩展性&＃xff1a;例如可以很容易实现分组查询排名第N高&＃xff0c;而这是简单的limit&＃43;offset写法所不能实现的。那么&＃xff0c;有没有既能拓展到分组查询、同时又具有单表查询的高效呢&＃xff1f;答案是肯定的&＃xff0c;例如下面的自定义变量写法&＃xff0c;通过设定一个自变量&＃xff0c;获取每个薪水的排名信息&＃xff0c;然后筛选排名为N的薪水即可。

SQL语句&＃xff1a;

1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3  RETURN (
4      SELECT 
5          DISTINCT salary 
6      FROM 
7          (SELECT 
8                salary, &＃64;r:&＃61;IF(&＃64;p&＃61;salary, &＃64;r, &＃64;r&＃43;1) AS rnk,  &＃64;p:&＃61; salary 
9            FROM  
10                employee, (SELECT &＃64;r:&＃61;0, &＃64;p:&＃61;NULL)init 
11            ORDER BY 
12                salary DESC) tmp
13      WHERE rnk &＃61; N
14  );
15END
执行效率&＃xff1a;

因为仅涉及到单表查询&＃xff0c;所以效率更高&＃xff0c;与直接用limit&＃43;offset效率相当。

---

解法6 窗口函数

实际上&＃xff0c;解法5中的自定义变量查询写法在MySQL8.0以后有相应的窗口函数可以实现。窗口函数在MySQL8.0版本首次引进&＃xff0c;而其他很多SQL语言则早已内置。具体而言&＃xff0c;对于本题获取"致密"排名的薪水&＃xff0c;用到的窗口函数就是dense_rank()。

SQL语句&＃xff1a;

1CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
2BEGIN
3  RETURN (
4        SELECT 
5            DISTINCT salary
6        FROM 
7            (SELECT 
8                salary, dense_rank() over(ORDER BY salary DESC) AS rnk
9             FROM 
10                employee) tmp
11        WHERE rnk &＃61; N
12  );
13END
实际执行过程和解法5是一样的&＃xff0c;只是调用内置函数写法更加简洁&＃xff0c;效率也与解法5相当并略高于后者。因为当前OJ系统应用MySQL5.6版本&＃xff0c;所以无法测试效率。

---

对比总结

以上用6种写法实现同一需求&＃xff0c;实际上这应该也代表了绝大多数写SQL查询的一般性思路&＃xff1a;

• 能用单表优先用单表&＃xff0c;即便是需要用group by、order by、limit等&＃xff0c;效率一般也比多表高

• 不能用单表时优先用连接&＃xff0c;连接是SQL中非常强大的用法&＃xff0c;小表驱动大表&＃43;建立合适索引&＃43;合理运用连接条件&＃xff0c;基本上连接可以解决绝大部分问题。但join级数不宜过多&＃xff0c;毕竟是一个接近指数级增长的关联效果

• 能不用子查询、笛卡尔积尽量不用&＃xff0c;虽然很多情况下MySQL优化器会将其优化成连接方式的执行过程&＃xff0c;但效率仍然难以保证

• 自定义变量在复杂SQL实现中会很有用&＃xff0c;例如LeetCode中困难级别的数据库题目很多都需要借助自定义变量实现

• 如果MySQL版本允许&＃xff0c;窗口函数是一个最优选择&＃xff0c;除了经典的获取3种排名信息&＃xff0c;还有聚合函数、向前向后取值、百分位等&＃xff0c;具体可参考官方指南(本号回复关键字"教程"提供网盘下载)

MySQL8.0内置窗口函数

如果你觉得文章不错的话&＃xff0c;分享、收藏、在看、留言666是对老表的最大支持。

老表Pro已经满了

所以大家加老表Max吧

每日留言

说说你最近遇到的一个编程问题&＃xff1f;

或者新学的一个小技巧&＃xff1f;

(字数不少于15字)

留言赠书

完整Python基础知识要点

Python小知识 | 这些技能你不会?(一)Python小知识 | 这些技能你不会?(二)Python小知识 | 这些技能你不会?(三)Python小知识 | 这些技能你不会?(四)

近期推荐阅读&＃xff1a;

【1】整理了我开始分享学习笔记到现在超过250篇优质文章&＃xff0c;涵盖数据分析、爬虫、机器学习等方面&＃xff0c;别再说不知道该从哪开始&＃xff0c;实战哪里找了【2】【终篇】Pandas中文官方文档&＃xff1a;基础用法6(含1-5)

好文章&＃xff0c;我在看❤️