计算层次结构中的直接子节点数量

Question

我在SQLite3数据库中有一个简单的分类层次结构，每一行存储适当的父ID或NULL。

我想要找出特定类别是否是叶子节点，基本上是通过确定每一行是否定义了父ID来确定，或者说，确定每一行的子行数。

---

表定义：

CREATE TABLE category (
    id INTEGER PRIMARY KEY AUTOINCREMENT
    name TEXT NOT NULL
    parent_id INTEGER DEFAULT NULL
);

样本数据:

id          name        parent_id 
----------  ----------  ----------
34          People      
35          Countries   
36          USA         35
37          Pop         36
38          Rock        36
39          Japan       35
40          Pop         39
42          Rock        39
43          J-Pop       40

期望输出:
原始数据加上每行子类别（子项）的数量。

id          name        parent_id   direct_children
----------  ----------  ----------  ---------------
34          People                  0
35          Countries               2
36          USA         35          2
37          Pop         36          0
38          Rock        36          0
39          Japan       35          2
40          Pop         39          1
42          Rock        39          0
43          J-Pop       40          0

---

这似乎很简单（？），但由于我通常在简单的JOIN之后就迷失了方向，所以到目前为止，我还没有做得很远。我查看了类似的问题，但它们要么是跨表连接，要么想要对整个层次结构中的所有子项进行更复杂的计数，而不仅仅是直接子项行。

更改表模式是可能的（例如，如果需要child_id或child_count），但我宁愿不这样做。

任何建议都将不胜感激。

- Christopher Orr

Answer 1

您可以使用子查询来实现这一点:

select  c.*
,       (select count(*) from category c2 where c2.parent_id = c.id) 
            as direct_children
from    category c

或者使用join：

select  parent.id
,       parent.name
,       parent.parent_id
,       count(child.id) as direct_children
from    category parent
left join    
        category child
on      child.parent_id = parent.id
group by
        parent.id
,       parent.name
,       parent.parent_id

- Andomar

---

啊，子查询就这么简单！在SQLite中两者都表现得很好。非常感谢。 - Christopher Orr