sql - 无法弄清楚这个 Postgresql 递归自连接

Question

大家好，我有下表:

+----+--------------+------------+----------+
| Id |     Name     |    Type    | ParentId |
+----+--------------+------------+----------+
|  1 | Team A       | team       | 2        |
|  2 | Initiative A | initiative | 3        |
|  3 | Initiative B | initiative | 4        |
|  4 | Initiative C | initiative | 5        |
|  5 | Product A    | product    | 6        |
|  6 | Product B    | product    | NULL     |
+----+--------------+------------+----------+

本质上我想做的是递归地自连接相同类型的父级，并且只保留相同类型的最高级别父级。为了说明我的意思，如果我对上表执行递归连接，我希望得到以下结果:

+---------+--------+---------------+--------------+------------+-----------+
| Team_Id |  Team  | Initiative_Id |  Initiative  | Product_Id |  Product  |
+---------+--------+---------------+--------------+------------+-----------+
|       1 | Team A |             4 | Initiative C |          6 | Product B |
+---------+--------+---------------+--------------+------------+-----------+

我尝试通过 WITH RECURSIVE CTE 进行递归连接，但我不知道如何在最终查询中“汇总”相同类型的父级。

Answer 1

以下将为您提供所需的项目列表 - 但您必须转置它们才能获得 1 行多列:

SELECT t1.* FROM table AS t1
LEFT JOIN table AS t2 ON t1.parentId = t2.id
WHERE t1.parentId IS NULL OR t1.type <> t2.type