SQL 展现祖孙关系

SQL中展现祖孙关系通常需要使用到递归查询（Recursive Query），以下是在MySQL数据库中展现祖孙关系的完整攻略：

1. 数据表准备

为了展现祖孙关系，我们需要至少一个包含如下字段的数据表：

• id: 行的唯一标识符
• name: 行的名称
• parent_id：行的父级id

可以使用如下的SQL语句创建一个简单的数据表，并插入一些数据：

CREATE TABLE family (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  parent_id INT
);

INSERT INTO family VALUES 
(1, '祖父', NULL),
(2, '父亲', 1),
(3, '母亲', 1),
(4, '叔父', 1),
(5, '堂哥', 4),
(6, '堂弟', 4);

1. 递归查询

接下来我们可以使用递归查询，查询出指定行的所有祖先和后代。下面是使用MySQL实现递归查询的示例代码：

WITH RECURSIVE family_tree AS (
    SELECT id, name, parent_id, 0 AS generation
    FROM family
    WHERE id = 2
  UNION ALL
    SELECT f.id, f.name, f.parent_id, ft.generation + 1
    FROM family f
    JOIN family_tree ft ON ft.parent_id = f.id
)
SELECT * FROM family_tree;

上述代码中的WITH语句定义了一个名为family_tree的递归查询公共表达式（CTE）。该CTE包含了两个查询组成的联合：

• 第一个查询返回指定行（id=2）的基本信息
• 第二个查询使用JOIN语句连接family和family_tree表，并通过parent_id将祖先和子孙节点联系起来

递归查询的终止条件由第一个查询中的WHERE子句定义，即找到指定行的原始父级节点（在本例中是祖父节点）。返回的结果集包括id、name、parent_id和generation四个字段，其中generation字段表示当前节点与根节点（指定行的最终祖先）之间的代数差别。

1. 示例

为了更好的理解递归查询，我们用一个例子来演示。假设我们要查询id为6的行的祖先和后代，这条记录表示某人的堂弟。可以使用以上代码在MySQL中执行以下查询：

WITH RECURSIVE family_tree AS (
    SELECT id, name, parent_id, 0 AS generation
    FROM family
    WHERE id = 6
  UNION ALL
    SELECT f.id, f.name, f.parent_id, ft.generation + 1
    FROM family f
    JOIN family_tree ft ON ft.parent_id = f.id
)
SELECT * FROM family_tree;

得到如下的结果：

+----+-------+-----------+------------+
| id | name  | parent_id | generation |
+----+-------+-----------+------------+
|  6 | 堂弟  |         4 |          0 |
|  4 | 叔父  |         1 |          1 |
|  1 | 祖父  |      NULL |          2 |
+----+-------+-----------+------------+

结果表明，堂弟的祖先依次为叔父、祖父。同时，因为堂弟没有子孙，结果集不包含任何后代。

另外，如果需要查询树状结构的数据，我们可以考虑使用递归查询的方式，对于每一个节点，找到其对应的子节点，以此类推，将祖孙关系展示出来。