Select a data record with a parent-child or tree relationship in Oracle

The parent-child relational record set or tree-like data record set is a common data organization form in software development. For example, the organizational structure in the office system, such as the province, city, and region in the user system, such as the product category in the e-commerce website, such as the message reply relationship in the message board system.

For this type of data, we often use the master-slave table or master-slave field method to meet structural requirements.

A master-slave table stores data at all levels in different data tables (for example, a data table in a large category and a small data table in another data table; for example, a data table is stored in a province, the city is stored in another data table). However, the problem is that the number of data tables must be created at each layer of the data, which is not conducive to the expansion of data structure layers, such as products in e-commerce websites, in the initial stage, there are only categories and small categories. As products increase, only categories and small categories cannot meet the classification requirements of products. At this time, you need to add a category concept, to meet this change, we need to add a data table of the middle class, which will lead to large changes. Therefore, the design of such table shards to store data of all levels has certain limitations. If another solution-master-slave field-is used, this problem can be better solved. That is to say, the province, city, and product categories and sub-categories can be better solved, these data with the same physical structure and different logical structures are stored in the same data table. The logical relationship between each data table is indicated by the ID and ParentID fields.

 

When we encounter such a data structure, we often want to use a data operation to select all the relevant data starting from a certain piece of data. For example, when you access a department, you want to present the Department and Department information at the same time. For example, when you get a BBS message, all the replies and replies of each reply must be selected once. This is the most common requirement. The following describes how to use an SQL statement in Oralce to obtain all the information about this entry point from a single entry point. Next we will simulate a BBS message and reply data to describe this SQL statement

 

Assume that we have the following structured data (T_Topic)

TopicID ParentID TopicTitle

1 null ask Oracle to select tree data

2 1 re: Ask Oracle to select tree data

3 1. In fact, this is enough.

4 1. I have the same problem.

5, 1!

6. Do not post spam ~

7 3 learning ing

8 3 is not an Oracle Method

9 6 I like things

10 9!

 

Note that the above records are actually related to the records with TopicID = 1 (subrecords or sun records are always records of future generations)

Now we need to use a statement to select all the descendant records recorded with TopicID 1. Please refer to the following SQL:

SELECT TopicID, ParentID, TopicTitle FROM T_TOPIC connect by prior TopicID = ParentID start with TopciID = 1

With this SQL statement, we can select all the child records of the TopiID = 1 record from the Table and their own records at a time. The key part of this statement is connect by prior... start with. The standard syntax of this statement is as follows:

Select from tablename connect by {PRIOR column name 1 = column name 2 | column name 1 = PRIOR column name 2} [start with];

The connect by keyword is used to set two associated fields. The PRIOR keyword is used to set the priority reference field, and the start with keyword is used to set the START point. When we see the description of these three keywords, we will surely think of a problem. Since we can give priority to different fields, since we can select all its subnodes through the root node, you can also select all the ancestor nodes through the subnodes, because the priority setting of PRIOR is to set the search direction. If PRIOR is set to self-node priority, all child nodes of the current node are selected. If PRIOR is set to parent node priority, all ancestor nodes can be obtained in reverse order, take the above data as an example. We use the "seal" record as the starting point and use the following SQL

SELECT TopicID, ParentID, TopicTitle FROM T_TOPIC connect by TopicID = PRIOR ParentID start with TopicID = 10

We can select the following data records at a time, that is, all the ancestor nodes starting from the "seal" record.

TopicID ParentID TopicTitle

10 9!

9 6 I like things

6. Do not post spam ~

5, 1!

1 null ask Oracle to select tree data

The above explains how to find all or all descendant nodes of a certain node through a starting point. You may say that I don't need to select all. I only need to select two layers, my tree can only display two layers. Well, yes, it will also be one of the common requirements, but it doesn't matter. We can add a new keyword Level and use this keyword to control the selected link layer. In specific usage, we still use the second SQL requirement as an example. Now we assume that we need to find the two-layer ancestor node from the "seal" data. What should we do? Let's look at SQL

SELECT Topic, ParentID, TopicTitle FROM T_TOPIC where level <= 3 connect by TopicID = PRIOR ParentID start with TopicID = 10

You must note that this SQL statement contains such a sub-statement LELVEL <= 3, which is used to limit the sub-statement at the selection level, this clause ensures that we can select three levels of nodes including nodes and their parent nodes above the two layers. The change in the LEVEL clause also makes you more abnormal [-_-!] For example, if I want to retrieve the grandfather node of the current node, set Level to Level = 3, remove a minor sign to satisfy the abnormal needs. The LEVEL can be set to common values greater than, less than, equal to, greater than or equal to, and less than or equal, it can even be set to between x and y, which is a very good keyword AND can satisfy many abnormal needs.

The above is the basic usage of connecty by... PRIOR... start. Of course, users' needs will always be BT, but I believe that the combination of the above basic applications will certainly meet your needs.