查询:
SELECT
t1.id,
(SELECT group_concat(id separator ', ')
FROM table1 t2
WHERE t2.sponsor = t1.id) AS children
FROM table1 t1
GROUP BY t1.id
结果:
| ID | CHILDREN |
-----------------
| 1 | 2, 3 |
| 2 | 4 |
| 3 | (null) |
| 4 | 5, 6 |
| 5 | (null) |
| 6 | (null) |
插入声明:
INSERT INTO table2
SELECT
t1.id,
(SELECT group_concat(id separator ', ')
FROM table1 t2
WHERE t2.sponsor = t1.id) AS children
FROM table1 t1
GROUP BY t1.id