sql - 从 SQL 递归查询中添加额外的列

Question

我有一个递归请求的问题。在我解释之前:我使用 PostgreSQL 8.4。

总而言之，我有 2 个表:t_object 和 t_packaget_object 有 3 列:id、name 和 package_idt_package 有 3 列:id、name 和 parent_id(这是一个非常示意性的表示。实际上，这是 Enterprise Architect 的项目数据库)

t_package.parent_id 是包的父包，你猜对了。当递归地跟随面包屑时，可以获得对象顶层包。例如:

TOPPACKAGE
|
+--PACKAGE
   |
   +--ANOTHERPACKAGE
      |
      +--ANOBJECT
      +--ANOTHEROBJECT

问题是:可以有不止一个顶级包......我的目标是创建一个基于 t_object 的 View ，其中包含一个包含每个对象的顶级包的额外列。

我成功地执行了一个获取对象顶层包的递归请求:

WITH RECURSIVE parents(package_id, name, parent_id) AS (
    SELECT t_package.package_id, t_package.name, t_package.parent_id
    FROM t_package
    WHERE t_package.package_id = (
        SELECT package_id
        FROM t_object
        WHERE name = 'The name of an object'
    )
    UNION
    SELECT t_package.package_id, t_package.name, t_package.parent_id
    FROM t_package, parents
    WHERE parents.parent_id = t_package.package_id
) SELECT * FROM parents WHERE parent_id = 0

然后，我尝试创建包含 t_object 的 View 以及额外的列...到目前为止没有成功!

我必须承认，我显然已经触及了 SQL 的极限，而且我不知道如何实现这一点:(

示例数据:

t_object:
id;name;package_id
1;'First object';11
2;'Second object';11
3;'Third object';14
4;'Fourth object';12

t_package:
id;name;parent_id
10;'First package';13
11;'Second package';10
12;'Third package';14
13;'First root package';0
14;'Second root package';0

(parent_id=0 表示根包)

所以层次结构是:

First root package
|
+--First Package
   |
   +--Second package
      |
       +--First object
       +--Second object

Second root package
|
+--Third object
+--Third package
    |
    +--Fourth object

我想要的结果:

t_object_with_root_package:
id;name;package_id;root_package_id
1;'First object';11;13
2;'Second object';11;13
3;'Third object';14;14
4;'Fourth object';12;14

谢谢你的帮助

Answer 1

这也适用于 8.4:

WITH RECURSIVE rcte(id, name, package_id, root_package_id) AS (
    SELECT    o.id, o.name, p.id, p.id
    FROM      t_package p
    LEFT JOIN t_object o  ON o.package_id = p.id
    WHERE     p.parent_id = 0
  UNION
    SELECT    o.id, o.name, c.id, p.root_package_id
    FROM      rcte p
    JOIN      t_package c ON c.parent_id  = p.package_id
    LEFT JOIN t_object o  ON o.package_id = c.id
)
SELECT   *
FROM     rcte
WHERE    id IS NOT NULL
ORDER BY id

但现在我只能在 9.6 上测试它: http://rextester.com/QCZPS53546

注意:这里的主要思想是先选择根实体。然后遍历每条路径，直到层次结构的叶子。此外，使用 LEFT JOIN 收集每个步骤中的每个 t_object(因此 rCTE 输出将包含 NULL，其中 t_package 没有任何 t_object)。