我有一个表，其中数据以分层方式存储。在下表中，我们为每个孩子指定了家长。
| CHILD_ID | PARENT_ID |
|---|---|
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 3 |
| 6 | 3 |
| 7 | 2 |
| 8 | 6 |
| 10 | 8 |
| 11 | 5 |
| 12 | 7 |
| 13 | 4 |
使用古老的connect by
with bom as (
select distinct connect_by_root(parent_id) root, child_id
from data
start with parent_id = 2
connect by prior child_id = parent_id
)
select b.root, count(distinct interest) ni
from bom b
join interests s on s.child_id in (b.root, b.child_id)
group by b.root
db<gt；小提琴