mysql 无限递归_mysql 向下无限递归（不使用函数，单纯sql）

表结构和数据

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,

@l := @l+1 as level

FROM table1,

(SELECT @id := 5, @l := 0 ) b

WHERE @id > 0

) ID, table1 DATA

WHERE ID._id = DATA.id

ORDER BY level;

根据这个父级查询方法，很容易可以写出查所有子级的，下面的查询 id=2 的所有子级

SELECT ID.level, DATA.* FROM(

SELECT

@ids as _ids,

( SELECT @ids := GROUP_CONCAT(id)

FROM table1

WHERE FIND_IN_SET(parent_id, @ids)

) as cids,

@l := @l+1 as level

FROM table1,

(SELECT @ids :=2, @l := 0 ) b

WHERE @ids IS NOT NULL

) id, table1 DATA

WHERE FIND_IN_SET(DATA.id, ID._ids)

ORDER BY level, id

评论 (0) 引用此答案  举报

0

2018/08/23 14:14

数据表的结构：

id

name

parent_id

1

Home

0

2

About

1

3

Contact

1

4

Legal

2

5

Privacy

4

6

Products

1

7

Support

1

create table menu(

id int auto_increment,

name VARCHAR(255),

parent_id int,

PRIMARY KEY(id)

) ENGINE=INNODB auto_increment=1 default charset = "utf8";

insert into menu(id, name, parent_id) values(1, 'Home', 0);

insert into menu(id, name, parent_id) values(2, 'About', 1);

insert into menu(id, name, parent_id) values(3, 'Contact', 1);

insert into menu(id, name, parent_id) values(4, 'Legal', 2);

insert into menu(id, name, parent_id) values(5, 'Privacy', 4);

insert into menu(id, name, parent_id) values(6, 'Products', 1);

insert into menu(id, name, parent_id) values(7, 'Support', 1);

SELECT T2.id, T2.name

FROM (

SELECT

@r AS _id,

(SELECT @r := parent_id FROM menu WHERE id = _id) AS parent_id,

@l := @l + 1 AS lvl

FROM

(SELECT @r := 5, @l := 0) vars, #查询id为5的所有上级

menu h

WHERE @r <> 0) T1

JOIN menu T2

ON T1._id = T2.id

ORDER BY 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

ORDER BY parent_id,id

) src

ORDER BY id