sql - PostgreSQL。如何在递归查询中为所有 child 保存第一个 ID？

Question

我有构建树的递归查询，与

CREATE TABLE geo (
    id int not null primary key, 
    parent_id int references geo(id),  
    name varchar(1000)
);

INSERT INTO geo 
(id, parent_id, name) 
VALUES 
(1, null, 'Планета Земля'),
(2, 1, 'Континент Евразия'),
(3, 1, 'Континент Северная Америка'),
(4, 2, 'Европа'),
(5, 4, 'Россия'),
(6, 4, 'Германия'),
(7, 5, 'Москва'),
(8, 5, 'Санкт-Петербург'),
(9, 6, 'Берлин');

WITH RECURSIVE r AS (
   SELECT id, parent_id, name, 1 AS level
  FROM geo
   WHERE id = 4

   UNION ALL

   SELECT geo.id, geo.parent_id, geo.name, r.level + 1 AS level
   FROM geo
      JOIN r
          ON geo.parent_id = r.id
)

SELECT * FROM r;

如何为组中的每个 child 选择根 ID？现在结果:

id | parent_id |      name       | level  
----+-----------+-----------------+-------  
  4 |         2 | Европа          |     1  
  5 |         4 | Россия          |     2  
  6 |         4 | Германия        |     2  
  7 |         5 | Москва          |     3  
  8 |         5 | Санкт-Петербург |     3  
  9 |         6 | Берлин          |     3  

但我需要的东西:

 id | parent_id |      name       | level| root_id   
----+-----------+-----------------+------+---------  
  4 |         2 | Европа          |     1| null  
  5 |         4 | Россия          |     2| 4  
  6 |         4 | Германия        |     2| 4  
  7 |         5 | Москва          |     3| 4  
  8 |         5 | Санкт-Петербург |     3| 4  
  9 |         6 | Берлин          |     3| 4  

Answer 1

您可以从非递归部分继承它，类似于您对 level 所做的:

WITH RECURSIVE r AS (
  SELECT id, parent_id, name, 1 AS level, id as root
  FROM geo
  WHERE id = 4

  UNION ALL

  SELECT geo.id, geo.parent_id, geo.name, r.level + 1 AS level, r.root
  FROM geo
    JOIN r ON geo.parent_id = r.id
)
SELECT id, parent_id, name, level, 
       nullif(root, id) as root 
FROM r;

nullif() 是必需的，因为您要为 id = root

的行显示 null

在线示例:http://rextester.com/BPP29069