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