什么是 PostgreSQL 横向子查询？

2021年7月23日 274点热度 0人点赞 0条评论

作者 | 不剪发的Tony老师责编 | 晋兆雨

出品 | CSDN博客

一般来说，SQL 子查询只能引用外查询中的字段，而不能使用同一层级中其他表中的字段。例如：

-- 错误示例

SELECT d.dept_name,       t.avg_salaryFROM department dJOIN (SELECT avg(e.salary) AS avg_salary      FROM employee e      WHERE e.dept_id = d.dept_id) t;SQL 错误 [42601]: ERROR: syntax error at end of input  位置：183

由于 JOIN 子句中的查询语句 t 引用了左侧 department 表中的字段，因此产生了语法错误。

为了解决以上问题，我们可以使用 PostgreSQL 提供的横向子查询（LATERAL subquery）。不过在介绍 LATERAL 关键字之前，我们先来回顾一下 SELECT 和 FROM 子句的含义。例如：

SELECT dept_id, dept_nameFROM department;

简单来说，我们可以将以上查询看作一个循环处理语句。使用伪代码实现的以上 SQL 语句如下：

for dept_id, dept_name in departmentloop     print dept_id, dept_nameend loop

对于 department 中的每一条记录，都执行 SELECT 语句指定的操作，以上示例简单的输出了每行记录。

SELECT 就像一个循环语句，而 LATERAL 就像是一个嵌套循环语句，对于左侧表中的每行记录执行一次子查询操作。例如，通过增加 LATERAL 关键字，我们可以修改第一个示例：

SELECT d.dept_name,       t.avg_salaryFROM department dCROSS JOIN LATERAL     (SELECT avg(e.salary) AS avg_salary      FROM employee e      WHERE e.dept_id = d.dept_id) t;

dept_name  |avg_salary            |-----------+----------------------+行政管理部  |    26666.666666666667|人力资源部  |13166.6666666666666667|财务部      | 9000.0000000000000000|研发部      | 7577.7777777777777778|销售部      | 5012.5000000000000000|保卫部      |                      |

CROSS JOIN LATERAL 右侧的查询可以引用左侧表中的字段，以上语句为 JOIN 左侧的每个部门返回了月薪总和。

LATERAL 可以帮助我们实现一些有用的分析功能，例如以下查询返回了每个部门月薪最高的 3 名员工：

SELECT d.dept_name, t.emp_name, t.salaryFROM department dLEFT JOIN LATERAL     (SELECT emp_name, salary      FROM employee e      WHERE e.dept_id = d.dept_id      ORDER BY salary DESC      LIMIT 3) tON TRUE;

dept_name  |emp_name|salary  |-----------+--------+--------+行政管理部  |刘备     |30000.00|行政管理部  |关羽     |26000.00|行政管理部  |张飞     |24000.00|人力资源部  |诸葛亮   |24000.00|人力资源部  |黄忠     | 8000.00|人力资源部  |魏延     | 7500.00|财务部      |孙尚香   |12000.00|财务部      |孙丫鬟   | 6000.00|研发部      |赵云     |15000.00|研发部      |周仓     | 8000.00|研发部      |关兴     | 7000.00|销售部      |法正     |10000.00|销售部      |简雍     | 4800.00|销售部      |孙乾     | 4700.00|保卫部      |         |        |

对于 department 中的每个部门，子查询 t 最多返回 3 个员工信息。我们使用了 LEFT JOIN LATERAL，从而保证了“保卫部”也会返回一条数据。

同样使用伪代码表示以上查询语句：

for d in departmentloop      for e in employee order by salary desc      loop           cnt++           if cnt <= 3           then               return e           else               goto next d           end      end loopend loop

通过 EXPLIAN 命令查看以上语句的执行计划：

EXPLAINSELECT d.dept_name, t.emp_name, t.salaryFROM department dLEFT JOIN LATERAL     (SELECT emp_name, salary      FROM employee e      WHERE e.dept_id = d.dept_id      ORDER BY salary DESC      LIMIT 3) tON TRUE;

QUERY PLAN                                                                                       |-------------------------------------------------------------------------------------------------+Nested Loop Left Join  (cost=8.17..4439.35 rows=540 width=250)                                   |  ->  Seq Scan on department d  (cost=0.00..15.40 rows=540 width=122)                            |  ->  Limit  (cost=8.17..8.17 rows=1 width=132)                                                  |        ->  Sort  (cost=8.17..8.17 rows=1 width=132)                                             |              Sort Key: e.salary DESC                                                            |              ->  Index Scan using idx_emp_dept on employee e  (cost=0.14..8.16 rows=1 width=132)|                    Index Cond: (dept_id = d.dept_id)                                            |

Nested Loop Left Join 说明 PostgreSQL 使用的就是嵌套循环算法。

原文链接：https://blog.csdn.net/horses/article/details/118769805


☞贾跃亭回应是否有回国打算：那必须的；顺丰将拿出 2 亿元给快递员加薪；OpenAI 宣布解散机器人团队|极客头条
☞“为了拿下 Offer，我在技术面试时迎合面试官，给了错误答案！”
☞Windows、Linux 纷纷被爆漏洞，黑客可直取 root 权限！

819500什么是 PostgreSQL 横向子查询？

什么是 PostgreSQL 横向子查询？

☞贾跃亭回应是否有回国打算：那必须的；顺丰将拿出 2 亿元给快递员加薪；OpenAI 宣布解散机器人团队|极客头条

☞“为了拿下 Offer，我在技术面试时迎合面试官，给了错误答案！”

☞Windows、Linux 纷纷被爆漏洞，黑客可直取 root 权限！

文章评论