子树 SQL 嵌套集的乘积计数

Question

请参阅http://mikehillyer.com/articles/managing-hierarchical-data- in-mysql/

在“嵌套集中的聚合函数”标题下，

我试图制定一个类似于给定示例的查询，但我希望它在子树级别工作，所以如果我查询 MP3 播放器我会得到如下结果集：

|NAME          |COUNT|
----------------------
|MP3 PLAYERS   |  2  | // 2 because 1 at this level and 1 at child level  
|FLASH PLAYERS |  1  |

Answer 1

假设自引用表 tree_node 创建如下：

CREATE TABLE tree_node
(
  id serial NOT NULL,
  parent integer,
  "desc" text,
  l integer,
  r integer,
  CONSTRAINT tree_node_pkey PRIMARY KEY (id)
);

可以使用以下 SQL 检索计数：

select count(*), p.id, p.desc from tree_node c, tree_node p
where c.l<=p.r
and c.l>=p.l
group by p.id, p.desc;