递归CTE-获取后代(多对多关系)

问题描述

给出一棵描述系统是如何由其通用部分组成的树(或更像是有向图).现在让这个系统例如人体及其各部分的结点.

Given a tree (or more like a directed graph) that describes how a system is composed by its generic parts. For now let this system be e.g. the human body and the nodes its body parts.

例如，3可能是具有左叶和右叶(6和9)的肝脏，在这两个肝脏中都有静脉(8)(也可以在以下位置找到)肝脏的任何未指定部位，因此是8-> 3)，也包括在舌头中(5).胸部(4)的肺(7)也有右叶，依此类推...(当然，肝脏中没有肺，6- > 7是合理的，因此此示例不是最佳示例，但您可以理解.)

So for instance 3 could be the liver that has a left and a right lobe (6 and 9), in both of which there are veins (8) (that can also be found at any unspecified place of the liver, hence 8->3) but also in the tongue (5). The lung (7) - which is in the chest (4) - also has a right lobe, and so on... (Well, of course there is no lung in the liver and also a 6->7 would be reasonable so this example wasn't the best but you get it.)

所以我在这样的数据库中存储了这些数据:

So I have this data in a database like this:

table: part
+----+------------+   id is primary key
| id | name       |
+----+------------+
|  1 | head       |
|  2 | mouth      |
|  3 | liver      |
|  4 | chest      |
|  5 | tongue     |
|  6 | left lobe  |
|  7 | lung       |
|  8 | veins      |
|  9 | right lobe |
+----+------------+

table: partpart
+-------+---------+   part&cont is primary key
| part  | cont    |   part is foreign key for part.id
+-------+---------+   cont is foreign key for part.id
|   2   |    1    |
|   3   |    1    |
|   5   |    2    |
|   6   |    3    |
|   7   |    3    |
|   7   |    4    |
|   8   |    3    |
|   8   |    5    |
|   8   |    6    |
|   8   |    9    |
|   9   |    3    |
|   9   |    7    |
+-------+---------+

---

我想要实现的目标:

我想查询在零件3中可以找到的所有零件，并期望得到这样的结果:

---

What I want to achieve:

I'd like to query all parts that can be found in part 3 and expecting a result like this one:

result of query
+-------+---------+
| part  | subpart |
+-------+---------+
|   3   |    6    |
|   3   |    7    |
|   3   |    8    |
|   3   |    9    |
|   6   |    8    |
|   7   |    9    |
|   9   |    8    |
+-------+---------+

我觉得以这种期望的格式获取结果是不可行的，但是将其作为一个相似的集还是很不错的，因为我的目的是像这样为用户显示数据:

I have the feeling that getting the result in this desired format is not feasible, still it would be great to have it as a similar set because my purpose is to display the data for the user like that:

3
├─ 6
│  └─ 8
├─ 7
│  └─ 9
│     └─ 8
├─ 8
└─ 9
   └─ 8

---

我如何尝试:

WITH RECURSIVE tree AS (

  SELECT part.id as part, partpart.cont (..where to define subpart?)
  FROM part JOIN partpart
  ON part.id = partpart.part
  WHERE part.id = 3

  UNION ALL

  SELECT part.id, partpart.cont
  FROM (part JOIN partpart
  ON part.id = partpart.part
  ), tree
  WHERE partpart.cont = tree.part

)

SELECT part, subpart FROM tree

这是我能做的最接近的事情，但是当然不起作用.

This is the closest I could do but of course it doesn't work.

推荐答案

问题已解决，这是我需要的查询，希望它也能对其他人有所帮助...

Problem solved, here is the query I needed, I hope it once helps someone else too...

WITH RECURSIVE graph AS (
  SELECT
    p.id AS subpart,
    pp.cont AS part
  FROM part p JOIN partpart pp
  ON p.id = pp.part
  WHERE pp.cont = 3
  UNION ALL
  SELECT
    part.id,
    partpart.cont
  FROM (part JOIN partpart
  ON part.id = partpart.part
  ), graph WHERE partpart.cont = graph.subpart
)
SELECT part, subpart, FROM graph

这篇关于递归CTE-获取后代(多对多关系)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！