本文通过SQL语句插入并聚合数据，展示了一个BOM（Bill of Materials）的详细结构，包括零件编号、数量及汇总数量。最终结果显示了从A、B、C、D、E、F、G、H等不同组件的详细信息，以及它们之间的层级关系和总数量。
INSERT INTO bom
SELECT 'A','B',3 FROM dual UNION ALL
SELECT 'A','C',2 FROM dual UNION ALL
SELECT 'A','D',4 FROM dual UNION ALL
SELECT 'B','E',2 FROM dual UNION ALL
SELECT 'B','F',3 FROM dual UNION ALL
SELECT 'D','G',6 FROM dual UNION ALL
SELECT 'D','H',5 FROM dual UNION ALL
SELECT 'E','I',3 FROM dual
SELECT part_no,
qty,
DBMS_AW.eval_number(substr(sys_connect_by_path(qty, '*'),2)) AS sum_qty
FROM bom
START WITH item_no = 'A'
CONNECT BY PRIOR part_no = item_no
ORDER SIBLINGS BY 1,2;
PART_NO QTY SUM_QTY
---------- ---------- ----------
B 3 3
E 2 6
I 3 18
F 3 9
C 2 2
D 4 4
G 6 24
H 5 20
8 rows selected
SQL>
扫一扫
1375
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
