在平常开发过程中,递归查询随处可见。话不多说,本人在项目中遇到的是编码和父级编码,需要逐渐查询。
1、表结构
2、SQL
SELECT id,
(SELECT REPLACE(GROUP_CONCAT(code),',','/') FROM ddm_file_directory
where INSTR(CONCAT(paths,',',result.id,','),concat(',',id,',')) ) as
code
FROM (
SELECT id,parent_id,code,
@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,'',
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 ddm_file_directory,
(SELECT @le:=0,@pathlevel:='', @pathall:='',@pathnodes:='') vv
) result