主页 / dba / 问题 / 204975
Accepted
user148246
Asked: 2018-04-26 13:14:31 +0800 CST

从 3 个分层表中获取所有后代

  • 772

我在数据库中有下表：

+-------------+   +-------------+-------------+   +---------------------------+
|  project1   |   |          project2         +   |         project3          |
+-------------+   +-------------+-------------+   +---------------------------+
| project1_id |   | project2_id | project1_id |   | project3_id | project2_id |
+-------------+   +-------------+-------------+   +---------------------------+
|    hnc1     |   |    hnc4     |     hnc1    |   |     hnc7    |    hnc4     |
|    hnc2     |   |    hnc5     |     hnc1    |   |     hnc8    |    hnc4     |
|    hnc3     |   |    hnc6     |     hnc3    |   |     hnc9    |    hnc6     |
+-------------+   +-------------+-------------+   +---------------------------+

我需要从所有三个表中检索给定 ID 的所有后代 - 如果在任何表中找到 ID 本身，也包括在内。

例如，如果我使用 ID “hnc4”进行查询，它应该得到：

+-------------+
| descendants |
+-------------+
|    hnc7     |
|    hnc8     |
|    hnc4     |
+-------------+

如果我使用 ID 'hnc1'进行查询：

+-------------+
| descendants |
+-------------+
|    hnc4     |
|    hnc5     |
|    hnc7     |
|    hnc8     |
|    hnc1     |
+-------------+

即使 ID 仅在最后一个表中，查询也必须有效project3。

我正在搜索并发现递归 CTE 可能很有用，但我不知道如何使用 3 个表来实现它。

关于如何获得正确结果的任何指导？

postgresql hierarchy

2 个回答

  1. Best Answer

    标识符不能参数化，所以我想不出用 rCTE遍历多个表的方法。

    对每个表进行假设UNIQUE或PRIMARY KEY约束，因此我们不必担心重复。

    我将查询包装在 SQL 函数中以简化 ID 的传递：

    CREATE OR REPLACE FUNCTION f_all_descendants_of(_id text)
  RETURNS TABLE (descendants text) AS
$func$
   WITH p2 AS (
      SELECT project2_id
      FROM   project2
      WHERE  project1_id = $1
         OR  project2_id = $1
      )

   SELECT project3_id AS descendants
   FROM   project3
   WHERE  project2_id IN (TABLE p2)
      OR  project3_id = $1

   UNION ALL TABLE p2

   UNION ALL
   SELECT project1_id
   FROM   project1
   WHERE  project1_id = $1;
$func$  LANGUAGE sql;

    SELECTonproject2进入 CTE p2，因此我们可以重用SELECTon的结果project3。project1和不需要同样的project3。

    称呼：

    SELECT * FROM f_all_descendants_of('hnc4');

    准确地产生您想要的结果。

    SQL小提琴。

    如果表很大，一定要有正确的索引。有关的：

    • 1

  2. 模式升级

    您不需要三个表来进行单一继承。您可以使用一张表来做到这一点。这不仅更快、更干净，而且在保持完整性方面更好，而且它允许超过三个级别的深度（n 级）

    CREATE TABLE foo (
  id         int  PRIMARY KEY,
  id_parent  int  REFERENCES foo,
  CHECK (id>id_parent)
);

INSERT INTO foo (id, id_parent)
VALUES
  (1,null),  -- null = no parent, ancestors
  (2,null),  -- all of these are separate trees.
  (3,null),
  ( 4, 1 ),
  ( 5, 1 ),
  ( 6, 3 ),
  ( 7, 4 ),
  ( 8, 4 ),
  ( 9, 6 );

    现在我们可以查询它，

    WITH RECURSIVE t(id, id_parent, level, path) AS (
  SELECT id, id_parent, 0, ARRAY[id]
  FROM foo
  WHERE id_parent IS NULL
  UNION ALL
    SELECT foo.id, foo.id_parent, level+1, path + foo.id
    FROM t
    JOIN foo ON (foo.id_parent = t.id)
)
SELECT *
FROM t;

 id | id_parent | level |  path   
----+-----------+-------+---------
  1 |           |     0 | {1}
  2 |           |     0 | {2}
  3 |           |     0 | {3}
  4 |         1 |     1 | {1,4}
  5 |         1 |     1 | {1,5}
  6 |         3 |     1 | {3,6}
  7 |         4 |     2 | {1,4,7}
  8 |         4 |     2 | {1,4,8}
  9 |         6 |     2 | {3,6,9}
(9 rows)

    这种模式称为“单表层次结构”或自引用表。

    向后兼容

    事实上，如果你想回到愚蠢的三表设计......

    创建一个执行最后一个查询的视图，

    CREATE VIEW foo_level1 AS
WITH RECURSIVE t(id, id_parent, level, path) AS (
  SELECT id, id_parent, 0, ARRAY[id]
  FROM foo
  WHERE id_parent IS NULL
  UNION ALL
    SELECT foo.id, foo.id_parent, level+1, path + foo.id
    FROM t
    JOIN foo ON (foo.id_parent = t.id)
)
SELECT *
FROM t WHERE level = 0;

    继续关注那个..

    CREATE VIEW foo_level2 AS [...] WHERE level = 1;
CREATE VIEW foo_level3 AS [...] WHERE level = 2;

    您的查询

    要获取我表中的所有祖先hcn4，4请更改终点并在相反方向构建它。

    WITH RECURSIVE t(id, id_parent, level, path) AS (
  SELECT id, id_parent, 0, ARRAY[id]
  FROM foo
  WHERE id = 4
  UNION ALL
    SELECT foo.id, foo.id_parent, level+1, path + foo.id
    FROM t
    JOIN foo ON (t.id = foo.id_parent)
)
 AS
  SELECT * FROM t;

    想要hcn1只需要将其切换为WHERE id = 1；

    WITH RECURSIVE t(id, id_parent, level, path) AS (
  SELECT id, id_parent, 0, ARRAY[id]
  FROM foo
  WHERE id = 1
  UNION ALL
    SELECT foo.id, foo.id_parent, level+1, path + foo.id
    FROM t
    JOIN foo ON (t.id = foo.id_parent)
)
SELECT *
FROM t;

 id | id_parent | level |  path   
----+-----------+-------+---------
  1 |           |     0 | {1}
  4 |         1 |     1 | {1,4}
  5 |         1 |     1 | {1,5}
  7 |         4 |     2 | {1,4,7}
  8 |         4 |     2 | {1,4,8}
(5 rows)

    等价的，f_all_descendants_of(int)变成，

    CREATE FUNCTION f_all_descendants_of(_id int)
RETURNS TABLE (id int, id_parent int, level int, path int[]) AS $$
  WITH RECURSIVE t(id, id_parent, level, path) AS (
    SELECT id, id_parent, 0, ARRAY[id]
    FROM foo
    WHERE id = _id
    UNION ALL
      SELECT foo.id, foo.id_parent, level+1, path + foo.id
      FROM t
      JOIN foo ON (t.id = foo.id_parent)
  )
  SELECT * FROM t;
$$ LANGUAGE sql
IMMUTABLE;
    • 0

相关问题