查找父子层次结构中最多7个级别的级别段

Question

父子表

>td style="text-align:right；">Riya拉梅什Rakesh

id	介绍人_id	name
1	NULL
3	1	Anand
4	2	Preetity

CREATE PROCEDURE get_tree (start_from INT)
BEGIN
DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp (member_id INT PRIMARY KEY, 
name VARCHAR(255),
level INT);
INSERT INTO tmp SELECT id, name, 0
FROM test
WHERE id = start_from;
REPEAT
INSERT IGNORE INTO tmp SELECT test.id, test.name, tmp.level + 1
FROM test
JOIN tmp ON tmp.member_id = test.introducer_id;
UNTIL !ROW_COUNT() END REPEAT;
SELECT * FROM tmp;
END

小提琴

---

我已经分享了我的sql版本的截图链接。snippoard.io/qbdB0A.jpg–AfreenB

您的SQL server版本为MariaDB 10.4.14Akina

对于您的服务器版本，请使用

WITH RECURSIVE
cte AS ( SELECT id member_id, name, 0 level
FROM test
WHERE id = @start_from 
UNION ALL
SELECT test.id, test.name, cte.level + 1
FROM test
JOIN cte ON cte.member_id = test.introducer_id 
-- WHERE cte.level < 7 )
SELECT * 
FROM cte;

小提琴