SQL级联展示父级名称

前言

数据库是这样结构:

例:我想通过降血压药的parent_id展示父级的药品信息----西药/心血管系统用药/降血压药

sql展示:

WITH RECURSIVE category_path AS (  
    SELECT   
        id,   
        category_name,   
        parent_id,   
        CAST(category_name AS CHAR(255)) AS path  
    FROM   
        config_drug_category  
    WHERE   
        id = #{id}  -- 从'强心药'的id开始  
    UNION ALL  
    SELECT   
        c.id,   
        c.category_name,   
        c.parent_id,   
        CONCAT_WS('/',c.category_name, cp.path)  
    FROM   
        config_drug_category c  
    JOIN   
        category_path cp ON c.id = cp.parent_id  
    WHERE   
        cp.parent_id != '0'  -- 继续向上追溯，直到顶级父节点  
)  
SELECT   
    path  
FROM   
    category_path  
WHERE   
    parent_id = '0';  -- 顶级父节点的条件是parent_id为'0'

输出结果:

{
    "msg": "操作成功",
    "code": 200,
    "data": {
        "path": "西药/心血管系统用药/降血压药"
    }
}