NOW的侧写
本篇的目的是给上篇文章收个尾，记录Lateral Joins和Common Table Expressions的相关知识。
Lateral Joins
我们先来复习一下什么是join.
通过离散数学我们知道笛卡尔积：两个集合 A 和 B 的笛卡尔积，记作 A × B，是所有可能的有序对 (a, b) 的集合，其中 a 属于 A，b 属于 B。
我们可以用下面的语句来表达：
SELECT * FROM TableA, TableB;或者：
SELECT * FROM TableA CROSS JOIN TableB;而笛卡尔积的结果很多是没有意义的，并非所有的序对都能够构成”关系“，关系是笛卡尔积的一个子集。我们需要一些条件来筛选出他们。
JOIN = 笛卡尔积 (CROSS JOIN) + 筛选/扩展
具体来说，JOIN 和笛卡尔积的核心不同点在于 JOIN 引入了一个“连接条件” (Join Condition)，通常在 ON 子句中定义。
假设有 Departments 表和 Employees 表。Employees (id, name, dept_id)，Departments (id, name)
SELECT *
FROM Employees e
INNER JOIN Departments d ON e.dept_id = d.id;这个查询的逻辑步骤是：
- 计算
Employees × Departments的笛卡尔积。这个结果集里会包含 “张三” 和 “技术部” 的组合，也会包含 “张三” 和 “销售部” 的组合（即使张三不在销售部）。 - 应用筛选条件
e.dept_id = d.id。 - 只有那些员工的
dept_id和部门的id相等的组合行才会被保留下来。那些不匹配的行（比如 “张三” 和 “销售部”）就被丢弃了。
好，我们回到Lateral Joins。LATERAL 是一个 SQL 标准关键字，它可以被用在 FROM 子句中。它涉及 “迭代”或“循环”模型 (Iterative / For-each loop)。执行过程中， 右表依赖于左表 。具体过程如下：
- 数据库首先从左表
A中获取一行数据。 - 然后，利用这一行数据中的值，作为参数去计算右侧的表表达式
B。这意味着对于左表的每一行，右侧的表B的内容都可能不同。 - 将从左表取出的这一行与计算出的右表
B的结果集进行连接。 - 重复步骤 1-3，直到处理完左表
A的所有行。
还是用上面的部门工资排名例子，我们要计算每个部门中工资最高的两个人：
SELECT
d.name AS department_name,
top_employees.name AS employee_name,
top_employees.salary
FROM
departments d
LEFT JOIN LATERAL ( -- 使用 LEFT JOIN LATERAL 以包含没有员工的部门
SELECT
*
FROM
employees e
WHERE
e.department_id = d.id -- 核心：这里引用了左表 d 的 id
ORDER BY
e.salary DESC
LIMIT 2
) AS top_employees ON TRUE; -- 对于 LATERAL, ON 条件通常是 TRUE他的执行逻辑如下：
- 取左表一行： 数据库从
departments表中取出第一行。d.id=1d.name='销售部'
- 执行 LATERAL 子查询： 数据库开始执行
LATERAL里的子查询。此时，左表的d.id(值为1) 就像一个参数被传入了子查询。这个子查询会扫描employees表，找到所有department_id为 1 的员工，按薪水排序，然后只取前两条。返还结果集top_employees - 连接结果： 数据库将左表取出的“销售部”这一行，与
LATERAL子查询返回的这两行结果进行连接。
然后开始第二轮循环。这个过程看起来效率不高，但是十分直观。
Common Table Expression (CTE)
CTE，也称为 WITH 子句 (WITH Clause)，是一个临时的、命名的结果集，它在单个 SQL 语句（如 SELECT, INSERT, UPDATE, DELETE）的执行范围内存在。
他的核心思想是将复杂的查询逻辑分解成一系列简单的、可读的、可重用的逻辑步骤。
一个典型的例子是这样的：
WITH department_avg_salary AS ( -- 步骤 1: 计算每个部门的平均薪水，并命名为 department_avg_salary SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ), max_avg_salary AS ( -- 步骤 2: 从上一步的结果中，找出最高的平均薪水，并命名为 max_avg_salary SELECT MAX(avg_salary) AS max_sal FROM department_avg_salary ) -- 步骤 3: 主查询，找出平均薪水等于最高平均薪水的部门 SELECT d.name, das.avg_salary FROM departments d JOIN department_avg_salary das ON d.id = das.department_id WHERE das.avg_salary = (SELECT max_sal FROM max_avg_salary);
它等价于这个代码：
-- 可读性较差
SELECT
d.name
FROM
departments d
JOIN
-- 子查询 1：计算每个部门的平均薪水
(SELECT department_id, AVG(salary) as avg_salary FROM employees GROUP BY department_id) AS dept_avg
ON
d.id = dept_avg.department_id
WHERE
dept_avg.avg_salary = (
-- 子查询 2：找出所有部门平均薪水中的最大值
SELECT MAX(avg_salary)
FROM (
SELECT AVG(salary) as avg_salary FROM employees GROUP BY department_id
) AS max_avg_sal
);其实就是把前者的具体表达式代入主进程中了，但是前者读起来更舒服，也更方便修改。
就像C语言中的UDF一样，这个CTE的核心在于递归。这个功能是无法替代的。
一个递归 CTE 包含两部分：
- 基础成员 (Anchor Member): 查询的起始点，不引用自身。
- 递归成员 (Recursive Member): 引用 CTE 自身，并与前一次的结果进行
JOIN。 - 两者通过
UNION ALL连接
一个问题是 employees 表中有一个 manager_id 列，指向其直属上级的 id。我们需要找出 ID 为 203 的员工 “吴九” 的所有上级领导，直到 CEO。这个代码是这样的。
WITH RECURSIVE employee_hierarchy AS (
-- 1. 基础成员 (Anchor Member)
SELECT
id,
name,
manager_id,
1 AS level
FROM
employees
WHERE
id = 203
UNION ALL
-- 2. 递归成员 (Recursive Member)
SELECT
e.id,
e.name,
e.manager_id,
eh.level + 1
FROM
employees e
JOIN
employee_hierarchy eh ON e.id = eh.manager_id
)
-- 3. 主查询
SELECT
level,
id,
name
FROM
employee_hierarchy;值得注意的是：
- 在递归成员的
JOIN子句中，被引用的employee_hierarchy代表的是上一轮迭代新产生的结果集 (Working Set)。 - 在递归过程结束后的主查询中，被引用的
employee_hierarchy代表的是整个递归过程中累积的所有结果 (Final Results)。
这说明了employee_hierarchy这个CTE是专门为递归所设计的。