mysql - 如何使用 SQL 的表父子关系仅选择一代层次树？

Question

假设有一个名为 people 的表，其中包含以下列:

• person_id(整数)
• parent_person_id(整数)
• 名称(可变字符)

现在，假设表格已经被填充并且名称值只是字母(A、B、C、D ...)。考虑到每个人的 parent ，我们最终得到一个像下面这样的分层树。这些值的格式为:

person_id, parent_person_id, 姓名

• 1, 1, A
• 2, 2, B
  • 3, 2, C
    • 4、3、D
  • 5, 2, 乙
    • 6, 5, 女
      • 7, 6, G
      • 8, 6, H

考虑上面的结构，A&B是第一代，C&E是第二代，D&F是第三代，G&H是第四代。对于第一代元素，parent_person_id 等于元素的 person_id。

我需要编写一个查询，允许我从树的某一代(第一代、第二代、第三代、第四代等)中选择名称。结果，我可以获得一个包含某一代用户名称的表。例如:

第一代

person name  |  parent name
A            |  A
B            |  B

第二代

person name  |  parent name
C            |  B
E            |  B

第三代

person name  |  parent name
D            |  C
F            |  E

第四代

person name  |  parent name
G            |  F
H            |  F

我想传递一个参数来定义每一代都应该在以下查询中列为子代。

SELECT
    child.name as 'person name',
    parent.name as 'parent name'
FROM
    people as child
JOIN 
    people as parent
ON 
    child.parent_person_id = parent.person_id
WHERE
    -- I NEED HELP HERE :)
;

有人对我如何实现这一目标有任何想法吗？欢迎任何帮助。

Answer 1

这是冗长和丑陋的，而且会很慢，而且仅限于 4 代，但我不知道还能怎么做。

SELECT person_name, parent_name FROM

    (SELECT child1.name AS person_name, parent1.name AS parent_name, '1' AS generation
    FROM people as child1
    JOIN people as parent1
    ON child1.parent_person_id = parent1.person_id AND child1.parent_person_id = child1.person_id

    UNION

    SELECT child2.name AS person_name, parent2.name AS parent_name, '2' AS generation
    FROM people as child2
    JOIN people as parent2
    ON child2.parent_person_id = parent2.person_id AND child2.parent_person_id <> child2.person_id AND parent2.parent_person_id = parent2.person_id

    UNION

    SELECT child3.name AS person_name, parent3.name AS parent_name, '3' AS generation
    FROM people as child3
    JOIN people as parent3
    ON child3.parent_person_id = parent3.person_id AND parent3.parent_person_id <> parent3.person_id
    JOIN people as grandparent1
    ON parent3.parent_person_id = grandparent1.person_id AND grandparent1.parent_person_id = grandparent1.person_id

    UNION

    SELECT child4.name AS person_name, parent4.name AS parent_name, '4' AS generation
    FROM people as child4
    JOIN people as parent4
    ON child4.parent_person_id = parent4.person_id AND parent4.parent_person_id <> parent4.person_id
    JOIN people as grandparent2
    ON parent4.parent_person_id = grandparent2.person_id AND grandparent2.parent_person_id <> grandparent2.person_id
    JOIN people as greatgrandparent
    ON grandparent2.parent_person_id = greatgrandparent.person_id AND greatgrandparent.parent_person_id = greatgrandparent.person_id
    ) AS tree

WHERE generation = ?