CTE:让所有的父母和孩子都在一份声明中

Question

我有一个有用的CTE例子。

我可以选择所有的父母和孩子。

但我如何在一份声明中选择所有的祖父母和所有的孩子呢？

在这个例子中，如果我给“父亲”作为输入，我想要祖父、父亲、儿子作为输出。

我用PostgreSQL。但我认为这个问题应该是标准SQL。

如果我使用PostgreSQL特定的语法，请纠正我。

DROP table if exists tree;

CREATE TABLE tree (
 id SERIAL PRIMARY KEY,
 name character varying(64) NOT NULL,
 parent_id integer REFERENCES tree NULL
);

insert into tree values (1, 'Grandfather', NULL);
insert into tree values (2, 'Father', 1);
insert into tree values (3, 'Son', 2);


-- -------------------------------------
-- Getting all children works  

WITH RECURSIVE rec (id) as
(
  SELECT tree.id, tree.name from tree where name='Father'

  UNION ALL

  SELECT tree.id, tree.name from rec, tree where tree.parent_id = rec.id

  

  )
SELECT *
FROM rec;

-- Result: 
--  id |  name  
-- ----+--------
--   2 | Father
--   3 | Son



-- -------------------------------------
-- Getting all parents works

WITH RECURSIVE rec (id) as
(
  SELECT tree.id, tree.name, tree.parent_id from tree where name='Father'

  UNION ALL

  SELECT tree.id, tree.name, tree.parent_id from rec, tree where tree.id = rec.parent_id
  )
SELECT id, name
FROM rec;

-- Result
-- id |    name     
-- ----+-------------
--  2 | Father
--  1 | Grandfather

以上是一个简化的工作示例。这棵树可以高达100层深。在“父亲”之上可以有几个层次的祖先，在下面可以有几个层次的后代。我要所有的祖先和后代。

Answer 1

如果您想要所有祖先和所有后代，您可以将这两个查询合并在一起。使用这两个CTE，然后使用一个简单的UNION：

WITH RECURSIVE 
    -- descendants 
    rec_d (id, name) AS
    (
      SELECT tree.id, tree.name FROM tree WHERE name = 'Father'
      UNION ALL
      SELECT tree.id, tree.name FROM rec_d, tree where tree.parent_id = rec_d.id
    ),
    --  ancestors
    rec_a (id, name, parent_id) AS
    (
      SELECT tree.id, tree.name, tree.parent_id FROM tree WHERE name = 'Father'
      UNION ALL
      SELECT tree.id, tree.name, tree.parent_id FROM rec_a, tree WHERE tree.id = rec_a.parent_id
    )
SELECT id, name FROM rec_a
UNION 
SELECT id, name FROM rec_d ;

如果上面没有任何错误，我们可以改进它：

• 将最后的UNION更改为UNION ALL，只将起始节点(S)放在其中一个CTE中。
• 使用JOIN .. ON代替隐式联接。
• 修正SELECT列列表与CTE列列表之间的不匹配。

查询内容如下：

WITH RECURSIVE 
    -- starting node(s)
    starting (id, name, parent_id) AS
    (
      SELECT t.id, t.name, t.parent_id
      FROM tree AS t
      WHERE t.name = 'Father'          -- this can be arbitrary
    ),
    descendants (id, name, parent_id) AS
    (
      SELECT s.id, s.name, s.parent_id 
      FROM starting AS s
      UNION ALL
      SELECT t.id, t.name, t.parent_id 
      FROM tree AS t JOIN descendants AS d ON t.parent_id = d.id
    ),
    ancestors (id, name, parent_id) AS
    (
      SELECT t.id, t.name, t.parent_id 
      FROM tree AS t 
      WHERE t.id IN (SELECT parent_id FROM starting)
      UNION ALL
      SELECT t.id, t.name, t.parent_id 
      FROM tree AS t JOIN ancestors AS a ON t.id = a.parent_id
    )
TABLE ancestors
UNION ALL
TABLE descendants ;