你能给我从sqlserver到mysql的模拟代码吗

Question

我们有

[以下数据][1][1] ：https://i.stack.imgur.com/oFSzj.png

在这里，我们在带有特定分隔符"的列中显示所有可能的父Id

WITH Hierarchy(ChildId, ChildName, ParentId, Parents)
AS
(
SELECT Id, Name, ParentId, CAST('' AS VARCHAR(MAX))
FROM UserType AS FirtGeneration
WHERE ParentId IS NULL    
UNION ALL
SELECT NextGeneration.Id, NextGeneration.Name, Parent.ChildId,
CAST(CASE WHEN Parent.Parents = ''
THEN(CAST(NextGeneration.ParentId AS VARCHAR(MAX)))
ELSE(Parent.Parents + '.' + CAST(NextGeneration.ParentId AS VARCHAR(MAX)))
END AS VARCHAR(MAX))
FROM UserType AS NextGeneration
INNER JOIN Hierarchy AS Parent ON NextGeneration.ParentId = Parent.ChildId    
)
SELECT *
FROM Hierarchy
OPTION(MAXRECURSION 32767)

[结果为：][2][2] ：https://i.stack.imgur.com/AV5Xw.png

你知道我怎么能用mysql得到同等的东西吗？

Answer 1

WITH RECURSIVE descendants AS
(
SELECT id, name, CAST(Name AS CHAR(500)) AS path
FROM usertype  
where parent_id is null
UNION ALL
SELECT t.id, t.name, CONCAT(d.path, ',', t.name)
FROM descendants d, usertype t
WHERE t.parent_id = d.id
)
SELECT * FROM descendants ORDER BY path;