如何让MySQL中单句实现无限层次父子关系查询

表结构和数据

CREATE TABLE table1(id int, name varchar(10), parent_id int)

INSERT table1 VALUES

(1, ‘Home’, 0),

(2, ‘About’, 1),

(3, ‘Contact’, 1),

(4, ‘Legal’, 2),

(5, ‘Privacy’, 4),

(6, ‘Products’, 1),

(7, ‘Support’, 2)

查询 id = 5 的所有父级

SELECT ID.level, DATA.* FROM(

SELECT

@id as _id,

( SELECT @id := parent_id

FROM table1

WHERE id = @id

) as _pid,

我代码中有一段类似的，也是网上找的，你可以参考下

SELECT CATE_CODE AS ID,PARENT_CODE AS 父ID ,levels AS 级数, paths AS 路径 FROM (

SELECT CATE_CODE,PARENT_CODE,

@le:= IF (PARENT_CODE = 0 ,0,

IF( LOCATE( CONCAT('|',PARENT_CODE,':'),@pathlevel)   >0  ,    

SUBSTRING_INDEX( SUBSTRING_INDEX(@pathlevel,CONCAT('|',PARENT_CODE,':'),-1),'|',1) +1

,@le+1) ) levels

, @pathlevel:= CONCAT(@pathlevel,'|',CATE_CODE,':', @le ,'|') pathlevel

, @pathnodes:= IF( PARENT_CODE =0,',0',

CONCAT_WS(',',

IF( LOCATE( CONCAT('|',PARENT_CODE,':'),@pathall) >0  ,

SUBSTRING_INDEX( SUBSTRING_INDEX(@pathall,CONCAT('|',PARENT_CODE,':'),-1),'|',1)

,@pathnodes ) ,PARENT_CODE  ) )paths

,@pathall:=CONCAT(@pathall,'|',CATE_CODE,':', @pathnodes ,'|') pathall

FROM  doc_category,

(SELECT @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vv

ORDER BY  PARENT_CODE,CATE_CODE

) src

ORDER BY CATE_CODE

---