mysql怎么递归查询下级_mysql 递归查询下级

CREATE TABLE `ys_recommendation_code_user` (

`id` bigint NOT NULL AUTO_INCREMENT,

`parent_user_id` int NOT NULL COMMENT '推荐者的用户id',

`user_id` int NOT NULL COMMENT '用户id',

`gmt_create` datetime NOT NULL,

`gmt_modified` datetime NOT NULL,

PRIMARY KEY (`id`) ,

INDEX `idx_parent_user_id_user_id` (`parent_user_id`, `user_id`)

)

COMMENT='推荐码-用户关联表';

2.插入数据

insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('2','100','102','0000-00-00 00:00:00','0000-00-00 00:00:00');

insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('3','100','103','0000-00-00 00:00:00','0000-00-00 00:00:00');

insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('4','101','104','0000-00-00 00:00:00','0000-00-00 00:00:00');

insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('5','101','105','0000-00-00 00:00:00','0000-00-00 00:00:00');

insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('6','101','106','0000-00-00 00:00:00','0000-00-00 00:00:00');

insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('7','104','107','0000-00-00 00:00:00','0000-00-00 00:00:00');

insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('8','104','108','0000-00-00 00:00:00','0000-00-00 00:00:00');

insert into `ys_recommendation_code_user` (`id`, `parent_user_id`, `user_id`, `gmt_create`, `gmt_modified`) values('9','104','109','0000-00-00 00:00:00','0000-00-00 00:00:00');

3.查询

SELECT user_id FROM (

SELECT t1.user_id,

IF(FIND_IN_SET(parent_user_id, @pids) > 0, @pids := CONCAT(@pids, ',', user_id), 0) AS ischild

FROM (

SELECT user_id,parent_user_id FROM ys_recommendation_code_user t ORDER BY parent_user_id, user_id

) t1,

(SELECT @pids := 这里是需要查询的user_id) t2

) t3 WHERE ischild != 0

4.查询结果 user_id = 100