如何使用单个SQL查询物料清单获取所有级别的数据 [英] How to get all levels data using single SQL query for bill of material
问题描述
我有很多物料清单，其中包含其原材料和半成品.半成品有自己的物料清单，其中包含原材料以及其他半成品等.
I have a lot of bill of material Items and it contains their raw material and semi-finished goods. The semi-finished goods have their own bill of material with raw material and further semi-finished goods and so on.
我想创建一个SQL查询，通过该查询我可以获取物料清单的详细信息，直到上一层.我将为父物料清单放置一个WHERE子句，并应将详细信息保留到最后一级.它不限于7个级别，有些项目甚至可以达到10或15个级别.
I want to create a SQL query by which I want to get the details of the bill of material up to the last level. I will put a WHERE clause for the parent bill of material and should get the details up to the last level. It is not limited to 7 levels, some items may even goto 10 or 15 levels.
下面是示例"A"的层次结构示例.
Below is an example of the hierarchy for example Item 'A'.
下面是表和其中的数据的结构.
Below is the structure of the table and data in it.
推荐答案
使用分层查询和过滤器仅获取叶节点.
Use a hierarchical query and filter to only get the leaf nodes.
Oracle设置:
CREATE TABLE table_name ( bom_code, rm_code, qty ) AS
SELECT 'A', 'B', 1 FROM DUAL UNION ALL
SELECT 'A', 'C', 2 FROM DUAL UNION ALL
SELECT 'A', 'D', 5 FROM DUAL UNION ALL
SELECT 'A', 'E', 3 FROM DUAL UNION ALL
SELECT 'A', 'F', 8 FROM DUAL UNION ALL
SELECT 'D', 'G', 2 FROM DUAL UNION ALL
SELECT 'D', 'H', 1 FROM DUAL UNION ALL
SELECT 'D', 'I', 1 FROM DUAL UNION ALL
SELECT 'D', 'J', 1 FROM DUAL UNION ALL
SELECT 'F', 'K', 1 FROM DUAL UNION ALL
SELECT 'F', 'L', 1 FROM DUAL UNION ALL
SELECT 'G', 'Z', 1 FROM DUAL UNION ALL
SELECT 'G', 'AA', 3 FROM DUAL UNION ALL
SELECT 'I', 'M', 4 FROM DUAL UNION ALL
SELECT 'I', 'N', 9 FROM DUAL UNION ALL
SELECT 'I', 'O', 7 FROM DUAL UNION ALL
SELECT 'N', 'P', 6 FROM DUAL UNION ALL
SELECT 'N', 'Q', 12 FROM DUAL UNION ALL
SELECT 'N', 'R', 4 FROM DUAL UNION ALL
SELECT 'N', 'S', 9 FROM DUAL UNION ALL
SELECT 'S', 'T', 3 FROM DUAL UNION ALL
SELECT 'S', 'U', 2 FROM DUAL UNION ALL
SELECT 'T', 'V', 1 FROM DUAL UNION ALL
SELECT 'T', 'W', 3 FROM DUAL UNION ALL
SELECT 'U', 'X', 5 FROM DUAL UNION ALL
SELECT 'U', 'Y', 8 FROM DUAL;
查询:
SELECT t.*,
SYS_CONNECT_BY_PATH( BOM_CODE, '-' ) || '-' || RM_CODE AS path,
LEVEL
FROM table_name t
WHERE CONNECT_BY_ISLEAF = 1
START WITH bom_code = 'A'
CONNECT BY PRIOR rm_code = bom_code;
输出:
BOM_CODE | RM_CODE | QTY | PATH | LEVEL
:------- | :------ | --: | :------------- | ----:
A | B | 1 | -A-B | 1
A | C | 2 | -A-C | 1
G | AA | 3 | -A-D-G-AA | 3
G | Z | 1 | -A-D-G-Z | 3
D | H | 1 | -A-D-H | 2
I | M | 4 | -A-D-I-M | 3
N | P | 6 | -A-D-I-N-P | 4
N | Q | 12 | -A-D-I-N-Q | 4
N | R | 4 | -A-D-I-N-R | 4
T | V | 1 | -A-D-I-N-S-T-V | 6
T | W | 3 | -A-D-I-N-S-T-W | 6
U | X | 5 | -A-D-I-N-S-U-X | 6
U | Y | 8 | -A-D-I-N-S-U-Y | 6
I | O | 7 | -A-D-I-O | 3
D | J | 1 | -A-D-J | 2
A | E | 3 | -A-E | 1
F | K | 1 | -A-F-K | 2
F | L | 1 | -A-F-L | 2
查询2 :
如果您希望沿路径获取总量，请使用相关子查询以相反的方式遍历层次结构:
If you want the total quantity along the path then use a correlated sub-query to traverse the hierarchy in the reverse direction:
SELECT t.*,
SYS_CONNECT_BY_PATH( BOM_CODE, '-' ) || '-' || RM_CODE AS path,
LEVEL,
( SELECT SUM( qty )
FROM table_name s
START WITH t.rm_code = s.rm_code
CONNECT BY PRIOR bom_code = rm_code ) AS total_qty
FROM table_name t
WHERE CONNECT_BY_ISLEAF = 1
START WITH bom_code = 'A'
CONNECT BY PRIOR rm_code = bom_code;
输出:
BOM_CODE | RM_CODE | QTY | PATH | LEVEL | TOTAL_QTY
:------- | :------ | --: | :------------- | ----: | --------:
A | B | 1 | -A-B | 1 | 1
A | C | 2 | -A-C | 1 | 2
G | AA | 3 | -A-D-G-AA | 3 | 10
G | Z | 1 | -A-D-G-Z | 3 | 8
D | H | 1 | -A-D-H | 2 | 6
I | M | 4 | -A-D-I-M | 3 | 10
N | P | 6 | -A-D-I-N-P | 4 | 21
N | Q | 12 | -A-D-I-N-Q | 4 | 27
N | R | 4 | -A-D-I-N-R | 4 | 19
T | V | 1 | -A-D-I-N-S-T-V | 6 | 28
T | W | 3 | -A-D-I-N-S-T-W | 6 | 30
U | X | 5 | -A-D-I-N-S-U-X | 6 | 31
U | Y | 8 | -A-D-I-N-S-U-Y | 6 | 34
I | O | 7 | -A-D-I-O | 3 | 13
D | J | 1 | -A-D-J | 2 | 6
A | E | 3 | -A-E | 1 | 3
F | K | 1 | -A-F-K | 2 | 9
F | L | 1 | -A-F-L | 2 | 9
db<>小提琴此处
这篇关于如何使用单个SQL查询物料清单获取所有级别的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
