使用分层查询和过滤器仅获取叶节点。
Oracle安装程序 ：
CREATE TABLE table_name ( bom_pre, rm_pre, 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, LEVELFROM table_name tWHERe CONNECT_BY_ISLEAF = 1START WITH bom_pre = 'A'ConNECT BY PRIOR rm_pre = bom_pre;
输出 ：
```
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** ：如果要沿路径计算总量，请使用相关子查询以相反的方式遍历层次结构： SELECt t.*,SYS_CONNECT_BY_PATH( BOM_CODE, '-' ) || '-' || RM_CODE AS path,LEVEL,( SELECT SUM( qty ) FROM table_name s START WITH t.rm_pre = s.rm_pre ConNECT BY PRIOR bom_pre = rm_pre ) AS total_qty FROM table_name t WHERe CONNECT_BY_ISLEAF = 1 START WITH bom_pre = 'A' ConNECT BY PRIOR rm_pre = bom_pre;**输出** ：> ```BOM_CODE | RM_CODE | QTY | PATH| LEVEL | TOTAL_QTY:------- | :------ | --: | :------------- | ----: | --------:A | B | 1 | -A-B| 1 | 1A | C | 2 | -A-C| 1 | 2G | AA | 3 | -A-D-G-AA | 3 | 10G | Z | 1 | -A-D-G-Z | 3 | 8D | H | 1 | -A-D-H | 2 | 6I | M | 4 | -A-D-I-M | 3 | 10N | P | 6 | -A-D-I-N-P | 4 | 21N | Q | 12 | -A-D-I-N-Q | 4 | 27N | R | 4 | -A-D-I-N-R | 4 | 19T | V | 1 | -A-D-I-N-S-T-V | 6 | 28T | W | 3 | -A-D-I-N-S-T-W | 6 | 30U | X | 5 | -A-D-I-N-S-U-X | 6 | 31U | Y | 8 | -A-D-I-N-S-U-Y | 6 | 34I | O | 7 | -A-D-I-O | 3 | 13D | J | 1 | -A-D-J | 2 | 6A | E | 3 | -A-E| 1 | 3F | K | 1 | -A-F-K | 2 | 9F | L | 1 | -A-F-L | 2 | 9
db
<>在这里拨弄
