Chapter 4 - hierarchical query of SQL query

4.5 Hierarchical query

• Hierarchical query syntax

  • select Name 1、 Name 2… from Table name start with Starting conditions connect by prior Recursive conditions

    • start with： Specify the starting point of the query , That is, which records to query from

    • connect by prior：

      • Specify a recursive condition to get the next record
      • prior The location is different , There will be unnecessary query results , See the example below for details .

• Yes male_familys Make hierarchical query

  • 

  • The hierarchy diagram is as follows ：

    • 

  • prior Placement position -（ The top-down ） example ：

    • Query grandpa's children's information

      • select * from male_familys start with person=‘ grandpa ’ connect by prior person_id= father_id;

  • 

    • Inquire about Dad's children and grandchildren

      • select * from male_familys start with person=‘ Dad ’ connect by prior person_id= father_id;

  • 

    • Inquire about my grandchildren

      • select * from male_familys start with person=‘ I ’ connect by prior person_id= father_id;

  • 

• prior Placement position -（ Top up ） example ：

  • Check the information of dad and his immediate family elders

    • select * from male_familys start with person=‘ Dad ’ connect by person_id=prior father_id;

  • 

    • Inquire about elder sister and immediate family elders

      • select * from male_familys start with person=‘ sister ’ connect by person_id=prior father_id;

  • 

    • Inquire about the information of grandchildren and their immediate relatives and elders

      • select * from male_familys start with person=‘ Grandson ’ connect by person_id=prior father_id;

  • 

• notes ： Careful control prior Different placement positions , Search results are also different ！ Be a thoughtful person …