SQL常用算法-递归表达式
语法函数
with as
with as 在之前的章节中已经用到，但是当时只是用于创建临时表使用，更大的作用在于组合成为递归表达式，分解层级。
常见场景
一、制造业中，BOM是所有成本分析的底层，BOM表通常由自身ID，父级ID等元素构成的层级表，通常我们需要获得所有的末级元素，用于计算整体的材料费用等
二、对于权限分配表，部门主数据也为带有层级关系的层级表，通常我们需要对已知的部门查询他的最上级部门，以此确认权限
在上面两种常见当中，数据结构都是以自身/上级的ID层级关系体现，无论是通过顶层查询底层还是通过底层查询顶层，使用with as递归表达式都能轻松获取我们所需要的数据。
示例
1.已知用户部门，查询他的最上级部门
WITH RECURSIVE DepartmentHierarchy AS (
SELECT
dept_id,
parent_dept_id,
0 AS level
FROM
departments
WHERE
dept_id = :input_dept_id -- 指定要查询的部门ID
UNION ALL
SELECT
d.dept_id,
d.parent_dept_id,
dh.level + 1
FROM
departments d
INNER JOIN
DepartmentHierarchy dh ON d.dept_id = dh.parent_dept_id
)
SELECT
dept_id,
parent_dept_id
FROM
DepartmentHierarchy
WHERE
level = (SELECT MAX(level) FROM DepartmentHierarchy);
2.已知产品编码，获得所有最下级材料
WITH RECURSIVE ProductMaterials AS (
SELECT
product_id,
material_id,
1 AS level
FROM
bom_table
WHERE
parent_material_id IS NULL -- 找到最上级产品
UNION ALL
SELECT
b.product_id,
b.material_id,
pm.level + 1
FROM
bom_table b
INNER JOIN
ProductMaterials pm ON b.parent_material_id = pm.material_id)
SELECT DISTINCT
product_id,
material_id
FROM
ProductMaterials
WHERE
(product_id, level) IN (
SELECT
product_id,
MAX(level) AS max_level
FROM
ProductMaterials
GROUP BY
product_id
);
