如何使用单个SQL查询物料清单获取所有级别的数据

使用分层查询和过滤器仅获取叶节点。

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
<>在这里拨弄