使用递归cte SQL获取复合部件组件

Question

大家好!有一个表存储了汽车的组件。一辆汽车由零部件组成，每一个细节都可能构成它的公司。在出口处，我想要一份所有使用细节的清单。为了做到这一点，我想使用递归CTE。患病程度无法事先得知。但我不明白如何实现它。

例如:

+---------+--------------+-----+--------------+
| main ID |     Name     | cID |  Component   |
+---------+--------------+-----+--------------+
|       1 | Car          |  11 | Engine       |
|       1 | Car          |  12 | Transmission |
|       1 | Car          |  13 | Body         |
|      11 | Engine       |  21 | Crankshaft   |
|      11 | Engine       |  22 | Piston       |
|      11 | Engine       |  23 | Valve        |
|      12 | Transmission |  31 | Carter       |
|      12 | Transmission |  32 | Differential |
|      12 | Transmission |  33 | Lead shaft   |
+---------+--------------+-----+--------------+ 

下面我想要得到一个列表:

+---------------+
|  Components   |
+---------------+
| Engine        |
| Crankshaft    |
| Piston        |
| Valve         |
| Transmission  |
| Carter        |
| Differential  |
| Lead shaft    |
| Body          |
+---------------+

请不要严格判断-这是我第一次使用递归CTE

Answer 1

您可以使用递归CTE在多个级别上遍历图。

例如:

with
n as (
select component, '/' || cid as path 
from t 
where main_id not in (select cid from t)
union all
select t.component, n.path || cid from n join t on t.main_id = n.cid
)
select component
from n
order by path

这只是一个例子。您可以根据需要添加更多列，并调整起始节点(通过修改锚成员)或遍历路径(通过修改递归成员)。