如何使用带有 SQL 的表父子关系仅选择一代层次树? [英] How do I select only one generation of a hierarchical tree using an table parent child relation with SQL?

问题描述

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

Let's say there is a table called people with the following columns:

• person_id(整数)
• parent_person_id(整数)
• 名称(varchar)

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

Now, let's say the table has being populated and the name values are just letters (A, B, C, D ...). Considering the parents of each we end up with a hierarchical tree like the one bellow. The values are on the format:

person_id、parent_person_id、姓名

person_id, parent_person_id, name

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

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

      Consider on the structure above, A & B as first generation, C & E as second generation, D & F as third generation and G & H as fourth generation. For the first generation elements, the parent_person_id is equal to the person_id of the element.

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

      I need to write a query that allows me to select the names from a certain generation(first, second, third, fourth, etc) of the tree. As a result I could get a table with the names of the user from a certain generation. For example:

      第一代

      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
      

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

      I would like to pass a parameter to define each generation should be listed as child on the following query.

      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 :)
      ;
      

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

      Does anyone have any ideas on how I can achieve this? Any help is welcome.

      推荐答案

      这个冗长丑陋，而且会很慢，而且限制到 4 代，但我不知道还能怎么做.

      This is verbose and ugly and will be slow, and it's limited to 4 generations, but I don't know how else it can be done.

      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 = ?
      

      这篇关于如何使用带有 SQL 的表父子关系仅选择一代层次树?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！