MySQL树状结构查询-sql语句

MySQL 8.0及以上版本：使用WITH RECURSIVE
WITH RECURSIVE是SQL中用于执行递归查询的语法，特别适合于处理层级结构或递归数据（如树形结构、图结构）。其基本语法结构如下：

WITH RECURSIVE CTE_name AS (
SELECT column1, column2, ...
FROM table_name
WHERE condition
UNION ALL
SELECT column1, column2, ...
FROM table_name t
JOIN CTE_name cte ON t.column = cte.column
WHERE condition
)
SELECT * FROM CTE_name;

一、使用某个顶级类别的值，查找其下所有子类
示例:查询某个分类及其所有子分类

WITH RECURSIVE MenuHierarchy AS (
SELECT id, name, parent_id,type
FROM system_menu
-- 替换为实际要查询的顶级ID的值
WHERE id = 2563
UNION ALL
SELECT c.id, c.name, c.parent_id,c.type
FROM system_menu c
JOIN MenuHierarchy ch ON c.parent_id = ch.id
)
SELECT * FROM MenuHierarchy ;

二、使用父类值查找所有子类

-- #父查子
WITH RECURSIVE tem_menu AS (
-- 非递归部分
SELECT id,name, parent_id,1 As level FROM system_menu
-- 确定 父Id的值
WHERE parent_id=0
UNION ALL
-- 递归部分
SELECT e.id,e.name, e.parent_id,eh.level + 1 FROM system_menu e
JOIN tem_menu eh oN e.parent_id = eh.id)

SELECT * FROM tem_menu ORDER BY level, name;