mysql递归查询所有父节点拼接父节点名称

只能查询单条
WITH RECURSIVE parent_cte AS (
SELECT id, name, parentId
FROM tbl_customer
WHERE id = 2 – 起始节点ID
UNION ALL
SELECT d.id, d.name, d.parentId
FROM tbl_customer d
INNER JOIN parent_cte p ON d.id = p.parentId
)
SELECT GROUP_CONCAT(name ORDER BY parentId ASC SEPARATOR ’ -> ') AS parent_names
FROM parent_cte ;
解释：

‌递归CTE（Common Table Expression）‌：

WITH RECURSIVE parent_cte AS (…) 定义了一个递归CTE，名为parent_cte。
基础查询部分（SELECT id, name, parentId FROM tbl_customer WHERE id = 2）选择了起始节点（id=2）。
递归查询部分（SELECT d.id, d.name, d.parentId FROM tbl_customer d INNER JOIN parent_cte p ON d.id = p.parentId）通过自连接递归地查找父节点。
‌字符串拼接‌：

SELECT GROUP_CONCAT(name ORDER BY parentId ASC SEPARATOR ’ -> ‘) AS parent_names FROM parent_cte 使用GROUP_CONCAT函数将所有父节点的名称按parentId升序拼接成一个字符串，并使用’ -> '作为分隔符。
默认查询所有，单条查询在最后的查询语句中添加条件即可
WITH RECURSIVE CategoryPath AS (
SELECT
id,
name,
parentId,
name AS path_name
FROM
tbl_customer
WHERE
parentId=0 – 假设顶级分类的parentId为0
UNION ALL
SELECT
c.id,
c.name,
c.parentId,
CONCAT(cp.path_name, ’ -> ', c.name) AS path_name
FROM
tbl_customer c
JOIN
CategoryPath cp ON c.parentId = cp.id
)
SELECT
id,
path_name AS full_path_name
FROM
CategoryPath;