使用PostgreSQL选择层次结构的某些级别

Question

我有一个使用枚举路径表示层次结构的表：

  id | name  | path  
-----+-------+-------
   1 | Bob   | 1
   2 | Joe   | 2
   3 | Kyle  | 2/3
   4 | Sarah | 2/4
   5 | Jim   | 5
   6 | Steve | 5/6
   7 | Adam  | 5/7
   8 | Frank | 5/7/8
   9 | Sue   | 5/7/9

我需要一个查询，返回给定记录的直属子记录，以及每个子记录下所有子记录的计数。

例如，针对Jim（id=5）的查询应该返回以下集合：

  id | name  | path  | subrecords
-----+-------+-------+------------
   6 | Steve | 5/6   | 0
   7 | Adam  | 5/7   | 2

---

尝试次数：

如果我这样做：

select did, name, path, SUBSTRING(path FROM '5/[^/]*$') as child_path from items where path ~ '5/.*';

我走了一段路。。。

 did | name  | path  | child_path 
-----+-------+-------+---------
   6 | Steve | 5/6   | 5/6
   7 | Adam  | 5/7   | 5/7
   8 | Frank | 5/7/8 | 
   9 | Sue   | 5/7/9 | 

但是记录8和9需要被汇总为7以下的计数。

我试过了：

select SUBSTRING(path FROM '5/[^/]*$') as child_path, COUNT(id) as count from items where path ~ '5/.*' GROUP BY child_path;

这让我着迷：

child_path | count 
----------+-------
          |     2
 5/7      |     1
 5/6      |     1

没有。

我怎样才能做到这一点？

Answer 1

我认为您可以在没有自联接的情况下完成此操作。

可以使用以下表达式选择给定节点的所有子体：path like '5' || '/' || id || '%'。然后，您可以通过选择最多5的子字符串和下一个id:来聚合它们

select left(path, length('5'||'/') + position('/' in split_part(path||'/', '5'||'/', 2)) - 1) as child,
       count(*) - 1 as numdescendants
from items
where path like '5' || '/' || '%'
group by child;

count(*)统计特定子代（包括该子代）的子代数。因此，- 1可以得到其后代的数量。

这样就可以用任何路径替换'5'。

Answer 2

在我看来，这就完成了任务。

SELECT i.id,
       i.name,
       i.path,
       count(ii.id) AS cnt
FROM items i
LEFT OUTER JOIN items ii
ON ii.id != i.id AND ii.path LIKE i.path || '%'
WHERE i.path ~ '5/[^/]*$'
GROUP BY i.id,
         i.name,
         i.path;

http://sqlfiddle.com/#！15/b5b4c/23