MySQL 8.0查询，根据下游内容递归计算层次结构中每个节点的大小

Question

这是在MySQL 8.0中。

如何使用递归查询自下而上计算每个类别节点(type=1(的大小。

create table hierarchy (
name     varchar(100),
location varchar(100),
type     int,
size     int,
parent_name varchar(100),
parent_location varchar(100)
) engine=InnoDB default charset=UTF8MB4;
truncate hierarchy;
insert into hierarchy values
('music', '/', 1, 0, NULL, NULL),
('classical', '/music', 1, 0, 'music', '/'),
('pop', '/music', 1, 0, 'music', '/'),
('bonjovi', /music/pop', 'pop', '/music'),
('its_my_life.mp3', '/music/pop/bonjovi', 2, 4092, 'bonjovi', '/music/pop'),
('bach', '/music/classical', 1, 'classical', '/music'),
('flute_e_min.mp3', '/music/classical/bach', 2, 1024, 'bach', '/music/classical'),
('sonata_no1_g.mp3, '/music/classical/bach', 2, 2048, 'bach', '/music/classical'),  
select * from hierarchy;

从层次结构中的给定节点开始，在本例音乐中，如何通过递归地将层次结构中(type=2(的文件大小相加来实现每个直系后代类别组(type=1(的大小？

name         location           parent_name   parent_location   total size
'music'      '/'                NULL          NULL              7164
'classical'  '/music'           'music'       '/'               3072
'pop'        '/music'           'music'       '/'               4092
'bonjovi'    '/music/pop'       'pop'         '/music'          4092
'bach'       '/music/classical' 'classical'   '/music'          3072
...

最初，类型为1的节点具有默认值。查询根据下游的内容递归地计算大小。

为了您的方便，小提琴空间：https://www.db-fiddle.com/f/wozz4B6TEVmU95RPrQxvYd/0

Answer 1

您可以用一对递归CTE来解决这个问题。第一个查找基于给定节点的所有类别(在本例中为music(，第二个查找与每个类别相关联的所有文件，并构建其大小的表，其中所有类别一直到根类别。然后将该表连接到类别表，并在每个级别汇总文件大小：

WITH RECURSIVE categories AS (
SELECT name, location, parent_name, parent_location, size
FROM hierarchy
WHERE name = 'music'
UNION ALL
SELECT h.name, h.location, h.parent_name, h.parent_location, h.size
FROM hierarchy h
JOIN categories c ON c.name = h.parent_name
WHERE h.type = 1
),
filesizes AS (
SELECT size, parent_name
FROM hierarchy
WHERE type = 2 AND parent_name IN (SELECT name FROM categories)
UNION ALL
SELECT f.size, h.parent_name
FROM hierarchy h
JOIN filesizes f ON f.parent_name = h.name
WHERE h.parent_name IS NOT NULL
)
SELECT c.name, c.location, c.parent_name, c.parent_location,
SUM(f.size) AS total_size
FROM categories c
JOIN filesizes f ON f.parent_name = c.name
GROUP BY c.name, c.location, c.parent_name, c.parent_location

输出：

name        location            parent_name     parent_location     total_size
music       /                   null            null                7164
classical   /music              music           /                   3072
pop         /music              music           /                   4092
bonjovi     /music/pop          pop             /music              4092
bach        /music/classical    classical       /music              3072

dbfiddle 演示