Note
Strictly speaking, this process is iteration not recursion, but RECURSIVE is the terminology chosen by the SQL standards committee.
WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
SELECT sub_part, part, quantity FROM parts WHERE part = ‘our_product’
UNION ALL
SELECT p.sub_part, p.part, p.quantity
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part
- 首先把第一个查询里面的内容(……where part = ‘our_product’)赋值给中间表里临时工作表included_parts的 三个列(sub_part, part, quantity), 作为初始值。下面接把该表看作一个临时工作表included_parts,对于union就对所有的重复的行进行清除仅保留一行,union all则不会。。
- 只要临时工作表included_parts不为空,进行下一步,否则查询结束。
- 用临时工作表included_parts和parts表结合查询,得到的结果放到一个中间表,对于union就对所有的重复的行进行清除仅保留一行,union all则不会。
- 把中间表的内容赋给临时工作表included_parts,对于union就对所有的重复的行进行清除仅保留一行,union all则不会。清空中间表节约内存。
- 进行第二步。
- 结束?官网的说明感觉很绕口,也没说怎么保存所有输出,但是差一下肯定有**其他表(例如:included_parts)**来保存所有输出的呗,把每个步骤的结果都插入到一个最终表里,变成你所看到的输出~
官网:https://www.postgresql.org/docs/current/queries-with.html