前言
数据库是这样结构:
例:我想通过降血压药的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": "西药/心血管系统用药/降血压药"
}
}