以前分类都是用递归,好傻,其实可以在确定几级分类后使用left join
mysql> SELECT * FROM ta A LEFT JOIN ta B ON A.ID = B.P_ID WHERE B.ID IS NOT NULL
;
+----+------+------+------+------+------+
| ID | P_ID | NAME | ID | P_ID | NAME |
+----+------+------+------+------+------+
| 1 | NULL | A | 7 | 1 | AA |
| 1 | NULL | A | 8 | 1 | AB |
| 1 | NULL | A | 9 | 1 | AC |
| 2 | NULL | B | 10 | 2 | BA |
| 2 | NULL | B | 11 | 2 | BB |
| 2 | NULL | B | 12 | 2 | BC |
+----+------+------+------+------+------+
6 rows in set (0.00 sec)

不知道谁还有其他方法