与和
问题描述:
PGSQL requsrive要求我需要一些帮助与递归请求与和
选择递归:
WITH RECURSIVE r (child,name,qty) AS (
SELECT l.item child, i.name as name, l.qty
FROM items
LEFT JOIN lines l on i.bom = l.bom
UNION
SELECT d.child, d.name, d.qty
FROM (
SELECT l.item child, i.name, l.qty
FROM items i
LEFT JOIN bom_lines l on i.bom = l.bom
) as d
JOIN r ON d.name = r.child
)
SELECT * FROM r;
,但它不会计算总的数量
答
WITH RECURSIVE r (child,name,qty) AS (
SELECT l.item child, i.name as name, l.qty
FROM items i
LEFT JOIN bom_lines l on i.bom = l.bom
UNION
SELECT d.child, d.name, d.qty+r.qty FROM (SELECT l.item child, i.name, l.qty
FROM items i
LEFT JOIN bom_lines l on i.bom = l.bom) as d
JOIN r
ON d.name = r.child
)
SELECT * FROM r;
你想这个“材料清单“仅针对特定项目的结果(在您的示例中为”A“),还是针对每个项目(可能是*项目)? (*即它存在于'bom_heads'中。) – pozs