mysql 递归查出每个用户的3个子代

Question

想一下子查出每个用户的3个子代

使用MYSQL 8.0 新特性-递归CTE，没有找到合适的方法来限制3个子代

相关代码

测试表数据：
DROP TABLE IF EXISTS member`;
CREATE TABLE member (
id int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
fid int(11) NULL DEFAULT 0 COMMENT '上级ID',
name text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '钱包名称',
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 141279 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表' ROW_FORMAT = Compact;

---

-- Records of member

---

INSERT INTO member VALUES (100001, 100000, '公司');
INSERT INTO member VALUES (100002, 100001, '大G');
INSERT INTO member VALUES (100003, 100001, 'Qw');
INSERT INTO member VALUES (100004, 100001, 'wuxian1');
INSERT INTO member VALUES (100005, 100004, 'wuxian2');
INSERT INTO member VALUES (100006, 100005, 'wuxian3');
INSERT INTO member VALUES (100007, 100006, 'wuxian4');
INSERT INTO member VALUES (100008, 100007, 'wuxian5');
INSERT INTO member VALUES (100009, 100008, 'wuxian6');
INSERT INTO member VALUES (100010, 100009, 'wuxian7');
INSERT INTO member VALUES (100011, 100010, 'guc1');
INSERT INTO member VALUES (100012, 100010, 'guc2');
INSERT INTO member VALUES (100013, 100010, 'guc3');
INSERT INTO member VALUES (100014, 100010, 'guc4');
INSERT INTO member VALUES (100015, 100010, 'guc5');
INSERT INTO member VALUES (100016, 100010, 'guc6');
INSERT INTO member VALUES (100017, 100010, 'guc7');
INSERT INTO member VALUES (100018, 100010, 'guc8');
INSERT INTO member VALUES (100019, 100018, 'bite1');
INSERT INTO member VALUES (100020, 100019, 'bite2');
INSERT INTO member VALUES (100021, 100020, 'bite3');
INSERT INTO member VALUES (100022, 100021, 'bite4');
INSERT INTO member VALUES (100023, 100022, 'bite5');
INSERT INTO member VALUES (100024, 100023, 'bite6');
INSERT INTO member VALUES (100025, 100001, 'zhangyixi');
INSERT INTO member VALUES (100026, 100024, 'bite7');
INSERT INTO member VALUES (100027, 100026, 'bite8');
INSERT INTO member VALUES (100028, 100027, 'bite9');
INSERT INTO member VALUES (100029, 100028, 'bite10');
INSERT INTO member VALUES (100030, 100001, 'Azura');
INSERT INTO member VALUES (100031, 100001, '默契');
INSERT INTO member VALUES (100032, 100001, 'TTGH');
INSERT INTO member VALUES (100033, 100032, '再创辉煌');
INSERT INTO member VALUES (100034, 100033, '感恩有你');
INSERT INTO member VALUES (100035, 100034, '流浪天涯');
INSERT INTO member VALUES (100036, 100035, '红尘有你');
INSERT INTO member VALUES (100037, 100036, '环游世界');
INSERT INTO member VALUES (100038, 100037, '妙境');
INSERT INTO member VALUES (100039, 100038, 'PPY');
INSERT INTO member VALUES (100040, 100039, 'BTTB');
INSERT INTO member VALUES (100041, 100040, 'GUY');
INSERT INTO member VALUES (100042, 100041, 'RNBN');
INSERT INTO member VALUES (100043, 100027, 'fendou1');
INSERT INTO member VALUES (100044, 100042, 'DSR');
INSERT INTO member VALUES (100045, 100012, '共赢未来');
INSERT INTO member VALUES (100046, 100045, '白衣');
INSERT INTO member VALUES (100047, 100046, '众赢');
INSERT INTO member VALUES (100048, 100013, '坤坤');
INSERT INTO member VALUES (100049, 100047, '小悟空');
INSERT INTO member VALUES (100050, 100049, '山城起飞');
INSERT INTO member VALUES (100051, 100047, 'qing');
INSERT INTO member VALUES (100052, 100013, '坤少');
INSERT INTO member VALUES (100053, 100051, 'yn8888');
INSERT INTO member VALUES (100054, 100001, '孙喆喆');
INSERT INTO member VALUES (100055, 100050, 'GUC（中国）');
INSERT INTO member VALUES (100056, 100055, '王胖子');
INSERT INTO member VALUES (100057, 100055, '阿牛');
INSERT INTO member VALUES (100058, 100050, 'nini1314');
INSERT INTO member VALUES (100059, 100055, 'RD1');
INSERT INTO member VALUES (100060, 100047, '于莉莉');
INSERT INTO member VALUES (100061, 100059, 'RD2');
INSERT INTO member VALUES (100062, 100061, 'RD3');
INSERT INTO member VALUES (100063, 100062, 'RD4');
INSERT INTO member VALUES (100064, 100063, 'RD5');
INSERT INTO member VALUES (100065, 100043, 'licong008');
INSERT INTO member VALUES (100066, 100064, '豆子1');
INSERT INTO member VALUES (100067, 100064, '梁韧主号');
INSERT INTO member VALUES (100068, 100066, '豆子2');
INSERT INTO member VALUES (100069, 100065, 'licong009');
INSERT INTO member VALUES (100070, 100067, '梁韧201');
INSERT INTO member VALUES (100071, 100069, 'licong001');
INSERT INTO member VALUES (100072, 100058, 'siyu1314');
INSERT INTO member VALUES (100073, 100068, '聚宝盆');
INSERT INTO member VALUES (100074, 100001, 'William84');
INSERT INTO member VALUES (100075, 100050, '道客1314');
INSERT INTO member VALUES (100076, 100001, '2');
INSERT INTO member VALUES (100077, 100001, '红');
INSERT INTO member VALUES (100078, 100071, '杜一宁');
INSERT INTO member VALUES (100079, 100072, 'wly123456');
INSERT INTO member VALUES (100080, 100047, 'wh77jj');
INSERT INTO member VALUES (100081, 100079, '心然');
INSERT INTO member VALUES (100082, 100078, 'abb');
INSERT INTO member VALUES (100083, 100082, 'xf3333');
INSERT INTO member VALUES (100084, 100078, 'abb');
INSERT INTO member VALUES (100085, 100084, 'xf6666');
INSERT INTO member VALUES (100086, 100047, '小坤');
INSERT INTO member VALUES (100087, 100085, '丁得住 7138');
INSERT INTO member VALUES (100088, 100087, '不可一世');
INSERT INTO member VALUES (100089, 100088, 'CDW2701');
INSERT INTO member VALUES (100090, 100084, '佳人有约');
INSERT INTO member VALUES (100091, 100086, '坤坤');
INSERT INTO member VALUES (100092, 100001, '，');
INSERT INTO member VALUES (100093, 100086, '轩');
INSERT INTO member VALUES (100094, 100086, '语');
INSERT INTO member VALUES (100095, 100047, 'haoxiang');
INSERT INTO member VALUES (100096, 100067, 'liang202');
INSERT INTO member VALUES (100097, 100091, '沉默');
INSERT INTO member VALUES (100098, 100091, 'yzx1314');
INSERT INTO member VALUES (100099, 100068, '禅心2019');
INSERT INTO member VALUES (100100, 100089, 'licong002');
INSERT INTO member VALUES (100101, 100050, 'kingspc88');
INSERT INTO member VALUES (100102, 100087, '丁得住 1313');
INSERT INTO member VALUES (100103, 100075, '耕田书生');
INSERT INTO member VALUES (100104, 100102, '孙慧1');
INSERT INTO member VALUES (100105, 100075, '陌白.');
INSERT INTO member VALUES (100106, 100100, 'licong003');
INSERT INTO member VALUES (100107, 100097, '胜利');
INSERT INTO member VALUES (100108, 100086, '轩轩');
INSERT INTO member VALUES (100109, 100097, '中路');
INSERT INTO member VALUES (100110, 100104, '孙小惠1319');
INSERT INTO member VALUES (100111, 100110, 'ZHC0001');
INSERT INTO member VALUES (100112, 100111, 'ZHC0002');
INSERT INTO member VALUES (100113, 100112, 'zhc1313zh');
INSERT INTO member VALUES (100114, 100047, '霸林');
INSERT INTO member VALUES (100115, 100113, 'zhc1314zh');
INSERT INTO member VALUES (100116, 100091, '薛简');
INSERT INTO member VALUES (100117, 100065, 'li0081');
INSERT INTO member VALUES (100118, 100115, 'yjc111');
INSERT INTO member VALUES (100119, 100065, 'li8002');
INSERT INTO member VALUES (100120, 100118, 'yjc112');
INSERT INTO member VALUES (100121, 100065, 'li8003');
INSERT INTO member VALUES (100122, 100120, 'lhp888');
INSERT INTO member VALUES (100123, 100095, '道翔');
INSERT INTO member VALUES (100124, 100047, '兔兔快运');
INSERT INTO member VALUES (100125, 100065, 'li8004');
INSERT INTO member VALUES (100126, 100115, 'wzy1314');
INSERT INTO member VALUES (100127, 100065, 'li8005');
INSERT INTO member VALUES (100128, 100065, 'li8006');
INSERT INTO member VALUES (100129, 100065, 'li8007');
INSERT INTO member VALUES (100130, 100065, 'li8008');
INSERT INTO member VALUES (100131, 100065, 'li8009');
INSERT INTO member VALUES (100132, 100091, '玖柒');
INSERT INTO member VALUES (100133, 100065, 'li8010');
INSERT INTO member VALUES (100134, 100065, 'li8011');
INSERT INTO member VALUES (100135, 100065, 'li8012');
INSERT INTO member VALUES (100136, 100132, '冬天的雪');
INSERT INTO member VALUES (100137, 100065, 'li8013');
INSERT INTO member VALUES (100138, 100065, 'li8014');
INSERT INTO member VALUES (100139, 100087, 'A01');
INSERT INTO member VALUES (100140, 100069, 'li9001');
INSERT INTO member VALUES (100141, 100087, 'A02');
INSERT INTO member VALUES (100142, 100069, 'li9002');
INSERT INTO member VALUES (100143, 100069, 'li9003');
INSERT INTO member VALUES (100144, 100069, 'li9004');
INSERT INTO member VALUES (100145, 100069, 'li9005');
INSERT INTO member VALUES (100146, 100069, 'li9006');
INSERT INTO member VALUES (100147, 100069, 'li9007');
INSERT INTO member VALUES (100447, 100069, 'li9008');
INSERT INTO member VALUES (100448, 100069, 'li9009');
INSERT INTO member VALUES (100449, 100069, 'li9010');
INSERT INTO member VALUES (100450, 100087, 'A03');
INSERT INTO member VALUES (100451, 100069, 'li9011');
INSERT INTO member VALUES (100452, 100069, 'li9012');
INSERT INTO member VALUES (100453, 100122, '陈俄冬');
INSERT INTO member VALUES (100454, 100069, 'li9013');
INSERT INTO member VALUES (100455, 100069, 'li9014');
INSERT INTO member VALUES (100456, 100055, '江宇兵');
INSERT INTO member VALUES (100457, 100011, '乐源商贸');
INSERT INTO member VALUES (100458, 100071, 'li1001');
INSERT INTO member VALUES (100459, 100085, 'zyy6666');
INSERT INTO member VALUES (100460, 100071, 'li1002');
INSERT INTO member VALUES (100461, 100457, '乐源科技');
INSERT INTO member VALUES (100462, 100049, 'z1737161');
INSERT INTO member VALUES (100463, 100071, 'li1003');
INSERT INTO member VALUES (100464, 100071, 'li1004');
INSERT INTO member VALUES (100465, 100071, 'li1005');
INSERT INTO member VALUES (100466, 100071, 'li1006');
INSERT INTO member VALUES (100467, 100071, 'li1007');
INSERT INTO member VALUES (100468, 100071, 'li1008');
INSERT INTO member VALUES (100469, 100105, '初遇');
INSERT INTO member VALUES (100470, 100071, 'li1009');
INSERT INTO member VALUES (100471, 100018, 'Good');
INSERT INTO member VALUES (100472, 100049, 'Amy95');
INSERT INTO member VALUES (100473, 100055, 'GCY');
INSERT INTO member VALUES (100474, 100105, '252256269');
INSERT INTO member VALUES (100475, 100051, 'jingnuo');
INSERT INTO member VALUES (100476, 100049, '青铜');
INSERT INTO member VALUES (100477, 100471, 'BBT');
INSERT INTO member VALUES (100478, 100471, 'ZXSJ');
INSERT INTO member VALUES (100479, 100049, '白银');
INSERT INTO member VALUES (100480, 100071, 'li1010');
INSERT INTO member VALUES (100481, 100049, '砖石');
INSERT INTO member VALUES (100482, 100105, '小新');
INSERT INTO member VALUES (100483, 100053, 'yu');
INSERT INTO member VALUES (100484, 100105, 'bosszhou');
INSERT INTO member VALUES (100485, 100071, 'li1011');
INSERT INTO member VALUES (100486, 100105, 'heheda');
INSERT INTO member VALUES (100487, 100105, '头戴钢盔照直冲');
INSERT INTO member VALUES (100488, 100018, 'yunfan');
INSERT INTO member VALUES (100489, 100071, 'li1012');
INSERT INTO member VALUES (100490, 100049, '张赔');
INSERT INTO member VALUES (100491, 100071, 'li1013');
INSERT INTO member VALUES (100492, 100071, 'li1014');
INSERT INTO member VALUES (100493, 100105, '陈总');
INSERT INTO member VALUES (100494, 100018, '美好');
INSERT INTO member VALUES (100495, 100089, 'CDW2702');
INSERT INTO member VALUES (100496, 100495, 'CDW2703');
INSERT INTO member VALUES (100497, 100105, 'dfansto');
INSERT INTO member VALUES (100498, 100046, '天下无双');
INSERT INTO member VALUES (100499, 100085, 'xf5555');
INSERT INTO member VALUES (100500, 100122, 'GSWallet');
INSERT INTO member VALUES (100501, 100046, '雄霸天下');
INSERT INTO member VALUES (100502, 100478, 'zxy1030');
INSERT INTO member VALUES (100503, 100085, 'xf8888');
INSERT INTO member VALUES (100504, 100496, 'Davidw');
INSERT INTO member VALUES (100505, 100046, '笑傲江湖');
INSERT INTO member VALUES (100506, 100085, 'xf7777');
INSERT INTO member VALUES (100507, 100085, 'xf4444');
INSERT INTO member VALUES (100508, 100101, 'wrm1349');
INSERT INTO member VALUES (100509, 100085, 'xf2222');
INSERT INTO member VALUES (100510, 100085, 'xf9999');
INSERT INTO member VALUES (100511, 100085, 'lhm3333');
INSERT INTO member VALUES (100512, 100511, 'zyy8888');
INSERT INTO member VALUES (100513, 100087, 'A04');
INSERT INTO member VALUES (100514, 100085, 'xf1111');
INSERT INTO member VALUES (100515, 100087, 'A05');
INSERT INTO member VALUES (100516, 100087, 'A06');
INSERT INTO member VALUES (100517, 100087, 'A07');
INSERT INTO member VALUES (100518, 100087, 'A08');
INSERT INTO member VALUES (100519, 100087, 'A09');
INSERT INTO member VALUES (100520, 100105, '我想当马云');
INSERT INTO member VALUES (100521, 100070, 'lsy118');
INSERT INTO member VALUES (100522, 100084, '天官');
INSERT INTO member VALUES (100523, 100018, '行者');
INSERT INTO member VALUES (100524, 100029, 'CYXT001');
INSERT INTO member VALUES (100525, 100122, '153219839');
INSERT INTO member VALUES (100526, 100122, '151660');
INSERT INTO member VALUES (100527, 100102, 'ylh8787');
INSERT INTO member VALUES (100528, 100527, 'czl123456');
INSERT INTO member VALUES (100529, 100527, 'GUC');
INSERT INTO member VALUES (100530, 100075, '豆儿');
INSERT INTO member VALUES (100531, 100091, 'yika');
INSERT INTO member VALUES (100532, 100075, '尘埃落定');
INSERT INTO member VALUES (100533, 100126, 'ql336677');
INSERT INTO member VALUES (100534, 100457, '乐园');
INSERT INTO member VALUES (100535, 100533, 'qxj336677');
INSERT INTO member VALUES (100536, 100126, '谢');
INSERT INTO member VALUES (100537, 100524, 'CYXT002');
INSERT INTO member VALUES (100538, 100027, 'bite81');
INSERT INTO member VALUES (100539, 100055, 'GUC');
INSERT INTO member VALUES (100540, 100027, 'bite82');
INSERT INTO member VALUES (100541, 100027, 'bite83');
INSERT INTO member VALUES (100542, 100058, 'nini1315');
INSERT INTO member VALUES (100543, 100058, 'nini1316');
INSERT INTO member VALUES (100544, 100072, 'siyu1315');
INSERT INTO member VALUES (100545, 100079, 'wly234567');
INSERT INTO member VALUES (100546, 100508, 'wrm1349a');
INSERT INTO member VALUES (100547, 100072, 'siyu1316');
INSERT INTO member VALUES (100548, 100545, 'wly345677');
INSERT INTO member VALUES (100549, 100072, 'siyu1317');
INSERT INTO member VALUES (100550, 100545, 'wly345678');
INSERT INTO member VALUES (100551, 100545, 'wly345679');
INSERT INTO member VALUES (100552, 100072, 'siyu1318');
INSERT INTO member VALUES (100553, 100545, 'CZH');
INSERT INTO member VALUES (100554, 100079, 'wly234568');
INSERT INTO member VALUES (100555, 100088, '伟伟');
INSERT INTO member VALUES (100556, 100512, 'wdw3333');
INSERT INTO member VALUES (100557, 100059, 'RD11');
INSERT INTO member VALUES (100558, 100059, 'RD12');
INSERT INTO member VALUES (100559, 100059, 'RD13');
INSERT INTO member VALUES (100560, 100059, 'RD4');
INSERT INTO member VALUES (100561, 100059, 'RD15');
INSERT INTO member VALUES (100562, 100059, 'RD16');
INSERT INTO member VALUES (100563, 100059, 'RD17');
INSERT INTO member VALUES (100564, 100059, 'RD18');
INSERT INTO member VALUES (100565, 100059, 'RD19');
INSERT INTO member VALUES (100566, 100059, 'RD110');
INSERT INTO member VALUES (100567, 100061, 'RD21');
INSERT INTO member VALUES (100568, 100061, 'RD22');
INSERT INTO member VALUES (100569, 100061, 'RD23');
INSERT INTO member VALUES (100570, 100061, 'RD24');
INSERT INTO member VALUES (100571, 100061, 'RD25');
INSERT INTO member VALUES (100572, 100061, 'RD26');
INSERT INTO member VALUES (100573, 100061, 'RD27');
INSERT INTO member VALUES (100574, 100477, '钱多多');
INSERT INTO member VALUES (100575, 100062, 'RD31');
INSERT INTO member VALUES (100576, 100062, 'RD32');
INSERT INTO member VALUES (100577, 100062, 'RD33');
INSERT INTO member VALUES (100578, 100062, 'RD34');
INSERT INTO member VALUES (100579, 100062, 'RD35');
INSERT INTO member VALUES (100580, 100062, 'RD36');
INSERT INTO member VALUES (100581, 100062, 'RD37');
INSERT INTO member VALUES (100582, 100063, 'RD41');
INSERT INTO member VALUES (100583, 100063, 'RD42');
INSERT INTO member VALUES (100584, 100063, 'RD43');
INSERT INTO member VALUES (100585, 100064, 'RD51');
INSERT INTO member VALUES (100586, 100064, 'TD52');
INSERT INTO member VALUES (100587, 100064, 'RD3');
INSERT INTO member VALUES (100588, 100070, '心想事成');
INSERT INTO member VALUES (100589, 100055, '凯歌');
INSERT INTO member VALUES (100590, 100508, '东岳');
INSERT INTO member VALUES (100891, 100545, 'ywb~1');
INSERT INTO member VALUES (100892, 100508, 'Aa1965150');
INSERT INTO member VALUES (100893, 100075, '道客158');
INSERT INTO member VALUES (100894, 100055, 'q55113150');
INSERT INTO member VALUES (100895, 100502, 'dl');
INSERT INTO member VALUES (100896, 100523, '实力派');
INSERT INTO member VALUES (100897, 100018, '爱德华');
INSERT INTO member VALUES (100898, 100075, '爱喝酸奶');
INSERT INTO member VALUES (100899, 100536, '涂');
INSERT INTO member VALUES (100900, 100126, 'zwq1314');
INSERT INTO member VALUES (100901, 100536, '有福222');
INSERT INTO member VALUES (100902, 100047, '手心的海');
INSERT INTO member VALUES (100903, 100508, 'wem1234');
INSERT INTO member VALUES (100904, 100047, '茗香居士');
INSERT INTO member VALUES (100905, 100896, '亚光');
INSERT INTO member VALUES (100906, 100545, 'CZH999');
INSERT INTO member VALUES (100907, 100059, 'RD111');
INSERT INTO member VALUES (100908, 100059, 'RD112');
INSERT INTO member VALUES (100909, 100059, 'RD113');
INSERT INTO member VALUES (100910, 100059, 'RD114');
INSERT INTO member VALUES (100911, 100061, 'RD28');
INSERT INTO member VALUES (100912, 100061, 'RD29');
INSERT INTO member VALUES (100913, 100061, 'RD210');
INSERT INTO member VALUES (100914, 100061, 'RD211');
INSERT INTO member VALUES (100915, 100061, 'RD212');
INSERT INTO member VALUES (100916, 100061, 'RD213');
INSERT INTO member VALUES (100917, 100061, 'RD214');
INSERT INTO member VALUES (100918, 100062, 'RD38');
INSERT INTO member VALUES (100919, 100062, 'RD39');
INSERT INTO member VALUES (100920, 100062, 'RD310');
INSERT INTO member VALUES (100921, 100062, 'RD311');
INSERT INTO member VALUES (100922, 100062, 'RD312');
INSERT INTO member VALUES (100923, 100062, 'RD313');
INSERT INTO member VALUES (100924, 100062, 'RD314');
INSERT INTO member VALUES (100925, 100063, 'RD44');
INSERT INTO member VALUES (100926, 100063, 'RD45');
INSERT INTO member VALUES (100927, 100063, 'RD46');
INSERT INTO member VALUES (100928, 100063, 'RD47');
INSERT INTO member VALUES (100929, 100063, 'RD48');
INSERT INTO member VALUES (100930, 100050, '卿彧');
INSERT INTO member VALUES (100931, 100064, 'RD54');
INSERT INTO member VALUES (100932, 100064, 'RD55');
INSERT INTO member VALUES (100933, 100064, 'RD56');
INSERT INTO member VALUES (100934, 100064, 'RD57');
INSERT INTO member VALUES (100935, 100027, 'bite84');
INSERT INTO member VALUES (100936, 100027, 'bite85');
INSERT INTO member VALUES (100937, 100027, 'bite86');
INSERT INTO member VALUES (100938, 100555, '宋国福');
INSERT INTO member VALUES (100939, 100088, '王宝晶');
INSERT INTO member VALUES (100940, 100939, '周万柱');
INSERT INTO member VALUES (100941, 100088, '刘丽伟');
INSERT INTO member VALUES (100942, 100027, 'bite87');
INSERT INTO member VALUES (100943, 100027, 'bite88');
INSERT INTO member VALUES (100944, 100063, 'RD49');
INSERT INTO member VALUES (100945, 100064, 'RD58');
INSERT INTO member VALUES (100946, 100011, 'guc1615');
INSERT INTO member VALUES (100947, 100064, 'RD59');
INSERT INTO member VALUES (100948, 100525, '150218373');`

查询语句
`WITH RECURSIVE test(id, name, path)
AS
(

SELECT id, name, CAST(id AS CHAR(2000))
FROM member WHERE id = 100001
UNION ALL
SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
FROM test AS ep JOIN member AS e ON ep.id = e.fid

)SELECT * FROM test ORDER BY path;`

你期待的结果是什么？

实际结果