MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ref` int(11) NOT NULL DEFAULT '0',
  `login` varchar(100) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `login` (`login`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `users` (`id`, `ref`, `login`) VALUES
    (3, 2, 'user'),   -- корень, 1 уровень
    (5, 3, 'user2'),  -- 2 уровень
    (6, 3, 'user3'),  -- 2 уровень
    (7, 6, 'user4'),  -- 3 уровень
    (8, 6, 'user5'),  -- 3 уровень
    (9, 7, 'user6'),  -- 4 уровень
    (10, 9, 'user7'); -- 5 (?) уровень
    
SELECT * FROM users;


id
ref
login


3
2
user

5
3
user2

6
3
user3

7
6
user4

8
6
user5

9
7
user6

10
9
user7


-- 1 уровень - те, кто не является чьими-то рефералами
SELECT id
FROM users t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM users t0
                   WHERE t0.id = t1.ref );


id


3


-- 2 уровень
SELECT t2.id
FROM users t1
JOIN users t2 ON t1.id = t2.ref
WHERE NOT EXISTS ( SELECT NULL
                   FROM users t0
                   WHERE t0.id = t1.ref );


id


5

6


-- 3 уровень
SELECT t3.id
FROM users t1
JOIN users t2 ON t1.id = t2.ref
JOIN users t3 ON t2.id = t3.ref
WHERE NOT EXISTS ( SELECT NULL
                   FROM users t0
                   WHERE t0.id = t1.ref );


id


7

8


-- 4 уровень
SELECT t4.id
FROM users t1
JOIN users t2 ON t1.id = t2.ref
JOIN users t3 ON t2.id = t3.ref
JOIN users t4 ON t3.id = t4.ref
WHERE NOT EXISTS ( SELECT NULL
                   FROM users t0
                   WHERE t0.id = t1.ref );


id


9


-- Все вместе
SELECT 1 AS level, id
FROM users t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM users t0
                   WHERE t0.id = t1.ref )
UNION ALL
SELECT 2, t2.id
FROM users t1
JOIN users t2 ON t1.id = t2.ref
WHERE NOT EXISTS ( SELECT NULL
                   FROM users t0
                   WHERE t0.id = t1.ref )
UNION ALL
SELECT 3, t3.id
FROM users t1
JOIN users t2 ON t1.id = t2.ref
JOIN users t3 ON t2.id = t3.ref
WHERE NOT EXISTS ( SELECT NULL
                   FROM users t0
                   WHERE t0.id = t1.ref )
UNION ALL
SELECT 4, t4.id
FROM users t1
JOIN users t2 ON t1.id = t2.ref
JOIN users t3 ON t2.id = t3.ref
JOIN users t4 ON t3.id = t4.ref
WHERE NOT EXISTS ( SELECT NULL
                   FROM users t0
                   WHERE t0.id = t1.ref )
ORDER BY level, id;


level
id


1
3

2
5

2
6

3
7

3
8

4
9


-- Все вместе, плюс id корневой записи
SELECT 1 AS level, id, id root_id
FROM users t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM users t0
                   WHERE t0.id = t1.ref )
UNION ALL
SELECT 2, t2.id, t1.id
FROM users t1
JOIN users t2 ON t1.id = t2.ref
WHERE NOT EXISTS ( SELECT NULL
                   FROM users t0
                   WHERE t0.id = t1.ref )
UNION ALL
SELECT 3, t3.id, t1.id
FROM users t1
JOIN users t2 ON t1.id = t2.ref
JOIN users t3 ON t2.id = t3.ref
WHERE NOT EXISTS ( SELECT NULL
                   FROM users t0
                   WHERE t0.id = t1.ref )
UNION ALL
SELECT 4, t4.id, t1.id
FROM users t1
JOIN users t2 ON t1.id = t2.ref
JOIN users t3 ON t2.id = t3.ref
JOIN users t4 ON t3.id = t4.ref
WHERE NOT EXISTS ( SELECT NULL
                   FROM users t0
                   WHERE t0.id = t1.ref )
ORDER BY level, id;


level
id
root_id


1
3
3

2
5
3

2
6
3

3
7
3

3
8
3

4
9
3