SELECT T2.id, T2.name
FROM(SELECT@rAS _id,(SELECT@r := parent_id FROM menu WHERE id = _id)AS parent_id,@l :=@l+1AS lvl
FROM(SELECT@r :=5,@l :=0) vars,#查询id为5的所有上级
menu h
WHERE@r<>0) T1
JOIN menu T2
ON T1._id = T2.id
ORDERBY T1.lvl DESC;
查询所有父到子之间级数和父到子路径
SELECT id AS ID,parent_id AS 父ID ,levels AS 父到子之间级数, paths AS 父到子路径 FROM(SELECT id,parent_id,@le:=IF(parent_id =0,0,IF( LOCATE( CONCAT('|',parent_id,':'),@pathlevel)>0,
SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',parent_id,':'),-1),'|',1)+1,@le+1)) levels
,@pathlevel:= CONCAT(@pathlevel,'|',id,':',@le,'|') pathlevel
,@pathnodes:=IF( parent_id =0,',0',
CONCAT_WS(',',IF( LOCATE( CONCAT('|',parent_id,':'),@pathall)>0,
SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',parent_id,':'),-1),'|',1),@pathnodes),parent_id ))paths
,@pathall:=CONCAT(@pathall,'|',id,':',@pathnodes,'|') pathall
FROM menu,(SELECT@le:=0,@pathlevel:='',@pathall:='',@pathnodes:='') vv
ORDERBY parent_id,id
) src
ORDERBY id