叶节点递归查询

Question

我需要查询方面的帮助。我只需要从下表中表示的子组件树中找到叶节点（这些组件不是由其他组件组成的 - 在这种情况下是 D、E、F）：

`CREATE TABLE COMPS
(
COMPONENT VARCHAR(8) NOT NULL,
SUBCOMPONENT VARCHAR(8) NOT NULL,
SUBCOMPCOUNT INTEGER NOT NULL
);
INSERT INTO COMPS VALUES('A', 'B', 2);
INSERT INTO COMPS VALUES('A', 'C', 3);
INSERT INTO COMPS VALUES('B', 'D', 4);
INSERT INTO COMPS VALUES('B', 'E', 2);
INSERT INTO COMPS VALUES('C', 'D', 2);
INSERT INTO COMPS VALUES('C', 'F', 3);
INSERT INTO COMPS VALUES('C', 'B', 2);
 WITH BILLSOFMATERIAL(COMPONENT, SUBCOMPONENT, PARTS_COUNT)
 AS(
 SELECT C.COMPONENT, C.SUBCOMPONENT, C.SUBCOMPCOUNT
 FROM COMPS C
 WHERE C.COMPONENT = 'A'
 UNION ALL
 SELECT B.COMPONENT, C.SUBCOMPONENT, B.PARTS_COUNT * C.SUBCOMPCOUNT
 FROM BILLSOFMATERIAL B, COMPS C
 WHERE B.SUBCOMPONENT = C.COMPONENT
 )
 SELECT COMPONENT, SUBCOMPONENT, SUM(PARTS_COUNT) AS "TOTAL QUANTITY"
 FROM BILLSOFMATERIAL
 GROUP BY COMPONENT, SUBCOMPONENT
 ORDER BY COMPONENT, SUBCOMPONENT;`

提前谢谢你。

Answer 1

最简单的过滤器是包含以下 WHERE 子句：

;WITH BILLSOFMATERIAL(COMPONENT, SUBCOMPONENT, PARTS_COUNT)
 AS(
 SELECT C.COMPONENT, C.SUBCOMPONENT, C.SUBCOMPCOUNT
 FROM COMPS C
 WHERE C.COMPONENT = 'A'
 UNION ALL
 SELECT B.COMPONENT, C.SUBCOMPONENT, B.PARTS_COUNT * C.SUBCOMPCOUNT
 FROM BILLSOFMATERIAL B, COMPS C
 WHERE B.SUBCOMPONENT = C.COMPONENT
 )
 SELECT COMPONENT, SUBCOMPONENT, SUM(PARTS_COUNT) AS "TOTAL QUANTITY"
 FROM BILLSOFMATERIAL
 WHERE SUBCOMPONENT NOT IN (SELECT COMPONENT FROM COMPS)
 GROUP BY COMPONENT, SUBCOMPONENT
 ORDER BY COMPONENT, SUBCOMPONENT;