mysql - 使用 MySQL 8.0 递归 CTE 在分层表中查找直接后代的数量并传播到父代
问题描述
我试图在层次结构中的每个节点上累积直系后代的数量。对于没有后代的节点,计数应为 0。
一般来说,我想在没有完全定义层次结构的多个上下文中应用不同类型的计数/聚合。因此,我对递归解决方案感兴趣。
考虑下面的代码。我如何“反转”这个查询,而不是计算深度,而是计算后代并向上传播数字?
create table hierarchy (
name varchar(100),
location varchar(100),
parent_name varchar(100),
parent_location varchar(100)
) engine=InnoDB default charset=UTF8MB4;
truncate hierarchy;
insert into hierarchy values
('music', '/', NULL, NULL),
('classical', '/music', 'music', '/'),
('pop', '/music', 'music', '/'),
('rock', '/music', 'music', '/'),
('bach', '/music/classical', 'classical', '/music');
select * from hierarchy;
with recursive cte as
(
select name, location, parent_name, parent_location, 1 as depth
from hierarchy where parent_name is NULL and parent_location is NULL
union all
select a.name, a.location, a.parent_name, a.parent_location, depth + 1
from hierarchy as a inner join cte on a.parent_name = cte.name and a.parent_location = cte.location
)
select *
from cte;
输出是
name location parent_name parent_location depth
'music' '/' NULL NULL 1
'classical' '/music' 'music' '/' 2
'pop' '/music' 'music' '/' 2
'rock' '/music' 'music' '/' 2
'bach' '/music/classical' 'classical' '/music' 3
我最终感兴趣的是这个输出:
name location parent_name parent_location descendents
'music' '/' NULL NULL 3
'classical' '/music' 'music' '/' 1
'pop' '/music' 'music' '/' 0
'rock' '/music' 'music' '/' 0
'bach' '/music/classical' 'classical' '/music' 0
解决方案
您似乎正在计算每个节点的直接后代的数量。如果是这样,我认为您不需要递归查询:一个简单的子查询应该可以做到:
select
h.*,
(select count(*) from hierarchy h1 where h1.parent_name = h.name) descendants
from hierarchy h
| name | location | parent_name | parent_location | descendants |
| --------- | ---------------- | ----------- | --------------- | ----------- |
| music | / | | | 3 |
| classical | /music | music | / | 1 |
| pop | /music | music | / | 0 |
| rock | /music | music | / | 0 |
| bach | /music/classical | classical | /music | 0 |
推荐阅读
- python - 使用 pandas 的左连接正在为同一行填充数据两次
- highcharts - 如何在 X 轴甘特图上提供样式
- flask - 由于网络错误而退出代码 1:ProtocolUnknownError
- django-rest-framework - DRF常用模型和外键
- flutter - 如何从颤动的蓝色中读取 tx 值
- cookies - 使用本地文件时将数据传递到 IE11 中的其他选项卡 (file://)
- sql - 如何检查用户过去是否有记录?
- android - React Native-Switching 选项卡不加载屏幕
- json - 我的代码中有什么错误,如何成功解析 json 中的数据?
- arrays - Golang:检查一个项目是否存在于数组中......然后检查它是否存在于两个不是它的项目之间