MySQL的族谱(family)表如下:
引用
+---------------------------+
| ID | PARENT | NAME |
+----+--------+-------------+
| 1 | 0 | 祖父 |
+----+--------+-------------+
| 2 | 1 | 父亲 |
+----+--------+-------------+
| 3 | 1 | 叔伯 |
+----+--------+-------------+
| 4 | 2 | 自己 |
+----+--------+-------------+
| 5 | 4 | 儿子 |
+----+--------+-------------+
| 6 | 5 | 孙子 |
+----+--------+-------------+
| 7 | 2 | 姐妹 |
+----+--------+-------------+
| 8 | 3 | 表亲 |
+----+--------+-------------+
| 9 | 7 | 甥儿 |
+----+--------+-------------+
| 10 | 4 | 女儿 |
+----+--------+-------------+
| 11 | 10 | 外孙 |
+----+--------+-------------+
| 12 | 5 | 孙女 |
+----+--------+-------------+
| .. | ... | .... |
+---------------------------+
以下是求id=4的所有后代的SQL
SELECT
p2 . *
FROM
family AS p1,
family AS p2
WHERE
p1.id = p2.parent AND
(
p1.id = 4 OR
p1.parent = 4
)
;
求取得id=11的祖先的SQL。
要求以一句SQL取得,最好不要用存储过程。
排序为从长到幼。
结果为:祖父->父亲->自已->女儿