父类别和子类别的MariaDB查询

Question

我有这个MariaDB表"类别"：

id  category        parent_id
1   electronics     0
2   fashion         0
3   TV              1
4   mobile          1
5   Sony            3
6   power bank      1
7   iphone          4

我想要使用MYSQL查询的结果

1 electronics
3 electronics >> TV
5 electronics >> TV >> Sony
4 electronics >> Mobile
7 electronics >> Mobile >> iphone
6 electronics >> Power bank
2 fashion

Answer 1

WITH RECURSIVE
cte AS ( SELECT id, category, parent_id, category path
FROM categories
WHERE parent_id = 0
UNION ALL
SELECT c.id, c.category, c.parent_id, CONCAT(cte.path, ' > ', c.category)
FROM categories c
JOIN cte ON cte.id = c.parent_id )
SELECT * FROM cte;

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=d9d59ace66a91b6b95c14e31cd1f6d1b