-- 查询该子节点的所有父节点(包括该子节点)
-- 表名称 db_menu
-- 表结构(menu_id,menu_name,menu_parent_id)
-- java传入子节点值:#{menuId}
-- sql如下,对照更改表名/字段名/参数名即可运行:
select r2.menu_id,r2.menu_name,r2.menu_parent_id from
(
SELECT
@r AS _id,
(SELECT @r := menu_parent_id FROM db_menu WHERE menu_id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM (SELECT @r := #{menuId},@l := 0) vars, db_menu h
)r1
JOIN db_menu r2 on r1._id=r2.menu_id ORDER BY r2.menu_id desc;