WITH RECURSIVE `cte` AS (
SELECT `id`, `pid`, `name`, 0 AS `level`
FROM `table`
UNION SELECT `p`.`id`, `p`.`pid`, `p`,`name`, `cte`.`level` + 1 AS `level`
FROM `cte`
JOIN `table` AS `p` ON `p`.`id` = `cte`.`pid`
)
SELECT `id`, `name`, `level`
FROM `cte`
ORDER BY `level`
