mysql 递归遍历所以子节点

mysql 递归遍历所以子节点
对应的建表语句

DROP TABLE IF EXISTS yun_portal_account_web;
CREATE TABLE yun_portal_account_web (
id int(0) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘自增ID，接口接入账号id’,
customerid int(0) UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘所属客户，客户表的ID’,
partnerid int(0) UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘上级账号id，0表示主账号’,
merchantid int(0) UNSIGNED NOT NULL DEFAULT 0 COMMENT ‘主账号id，0表示主账号’,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2000001 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = ‘用户表’ ROW_FORMAT = Dynamic;

---

– 添加数据

---

INSERT INTO yun_portal_account_web VALUES (1, 1001, 0, 0);
INSERT INTO yun_portal_account_web VALUES (2, 1001, 1, 0);
INSERT INTO yun_portal_account_web VALUES (3, 1001, 1, 0);
INSERT INTO yun_portal_account_web VALUES (4, 1002, 2, 0);
INSERT INTO yun_portal_account_web VALUES (5, 1001, 4, 0);
INSERT INTO yun_portal_account_web VALUES (6, 1003, 0, 1);
INSERT INTO yun_portal_account_web VALUES (7, 1004, 1, 1);

SET FOREIGN_KEY_CHECKS = 1;

查询语句：

select id from (
select t1.id,
if(find_in_set(partnerid, @pids) > 0, @pids := concat(@pids, ‘,’, id), 0) as ischild
from (
select id,partnerid from yun_portal_account_web t where t.merchantid= 0 order by partnerid, id
) t1,
(select @pids :=1 ) t2
) t3 where ischild != 0