Resursive category concat name only leaf mysql query

Question

如何使用mysql仅合并叶类别名称的名称？

样品表

>

id	name	parentid	active
1
2测试	1测试1测试-2测试-3测试-4测试-5测试2测试2-1测试2-2测试3

我尝试过mysql查询，但没有得到我想要的的确切结果

SELECT t1.id,t1.active,
CONCAT_WS(' > ', t8.name,t7.name,t6.name,t5.name, t4.name, t3.name, t2.name, t1.name) as path
FROM tablename AS t1
LEFT OUTER JOIN tablename AS t2 ON t2.id = t1.parentid
LEFT JOIN tablename AS t3 ON t3.id = t2.parentid 
LEFT JOIN tablename AS t4 ON t4.id = t3.parentid
LEFT JOIN tablename AS t5 ON t5.id = t4.parentid
LEFT JOIN tablename AS t6 ON t6.id = t5.parentid
LEFT JOIN tablename AS t7 ON t7.id = t6.parentid
LEFT JOIN tablename AS t8 ON t8.id = t7.parentid
WHERE
t1.active = 'H'

SELECT c.i AS child_id, c.name AS child_name, c.parentid AS parent_id , p.name AS parent_name, IF( c.parentid, CONCAT( p.name, ' > ', c.name ), c.name ) AS path
FROM tablename AS c
LEFT JOIN tablename AS p ON c.parentid = p.id
WHERE c.active='H'
ORDER BY child_id

WITH RECURSIVE
cte AS ( SELECT id, parentid current, name path
FROM test t1
WHERE NOT EXISTS ( SELECT NULL
FROM test t2
WHERE t1.id = t2.parentid)
UNION ALL
SELECT cte.id, test.parentid, CONCAT(test.name, '>', cte.path)
FROM cte
JOIN test ON test.id = cte.current )
SELECT id, path
FROM cte
WHERE !current

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f57e22bd20d2e9df4e572ec73f1d31b0

非常感谢Akina。除了这个代码，我们应该为那些具有active='E'的代码添加什么？

好的，我解决了我的问题。感谢

WITH RECURSIVE
cte AS ( SELECT id, parentid current, name path
FROM test t1
WHERE t1.active='E' AND NOT EXISTS ( SELECT NULL
FROM test t2
WHERE t1.id = t2.parentid)
UNION ALL
SELECT cte.id, test.parentid, CONCAT(test.name, '>', cte.path)
FROM cte
JOIN test ON test.id = cte.current )
SELECT id, path
FROM cte
WHERE !current