MYSQL递归查询

表结构如下:

SELECT * FROM parents;
+------+--------------+-------------+
| P_ID | P_NAME | P_PARENT_ID |
+------+--------------+-------------+
| 1 | me | 2 |
| 2 | dad | 3 |
| 3 | granddad | NULL |
| 4 | moi | NULL |
| 5 | kiddie1 | 4 |
| 6 | kiddie2 | 4 |
| 7 | grandkiddie1 | 5 |
+------+--------------+-------------+

递归查询语句:

SELECT p2.P_NAME as kids
FROM parents AS p1, parents AS p2
WHERE p1.P_ID = p2.P_PARENT_ID
AND (p1.P_ID=4 OR p1.P_PARENT_ID=4);
+--------------+
| kids |
+--------------+
| kiddie1 |
| kiddie2 |
| grandkiddie1 |
+--------------+