计算层次结构中直接子级的数目

我在一个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

这看起来可能很简单(?)但是,由于我通常会迷失在简单的连接之外,所以到目前为止,我对这个还没有太多了解。我检查过类似的问题,但它们似乎是跨表联接的,或者是对整个层次结构中的所有子级进行更复杂的计数,而不仅仅是直接子级行。

更改表模式是一种可能(例如,如果需要子级\u i d或子级\u count),但我宁愿不这样做。

任何意见都会受到赞赏。