Database tree query, database tree

Database tree query, database tree

During this period of time, I worked with others to develop a new function in my internship company. I am mainly focusing on database operations! One of them is data validation, and this data exists in the database as a tree! I was planning to do it cyclically before I asked others. I spent a whole afternoon studying the data table on Sunday and found that it was not as simple as I thought, first, let me briefly describe this requirement:

First, there are too many fields in the data table. I only find some useful fields:

Id is the primary key, which is automatically generated by the database. itemtype is the type. There are two types of data, one is classification, the other is entry (the next image will understand), and the name is the name, parentid is the immediate superior id (if you have been familiar with tree-structure data tables before, you will be familiar with parentid)

The following figure shows the data display result:

Yes, this is an EXCEL table. You can compare the above data with this table. The data behind each row is manually added, representing the current id.

For A brief explanation, if parentid is null, it indicates column A, that is, the highest level node. For example, if id is 8 and 9, and parentid is 8 or 9, it indicates Column B data, then column C, and so on... At the last level, the itemtype is 1, the rest are classification, and the itemtype is 0, 8 ~ 13 is classification, 14 ~ 23 is the entry. After the introduction, the following requirements are met: All the superiors of each entry (including itself, there must be only one category or entry specifying a valid base score (the valid base score is the field in other data tables. The link between them is id)

I don't know what kind of methods will be used for verification after reading the data table. I remember that afternoon, after I understood the data table, I checked it cyclically, because I have never heard of such a tree data table before, this is the first time I have come into contact with it...

The next day, I went to work and asked the old employee what methods she would use for verification. She said, "query by tree !" Why? What is tree query! I rushed to ask du Niang, a gentle du Niang, and said "Snap" to list millions of records. I simply looked at it and looked for the answers I could use!

Tree query syntax:

Select... from tablename start with condition 1 connect by condition 2 where Condition 3;

Condition 1 is a restricted statement of the root node, that is, the restriction conditions can be relaxed to obtain multiple root nodes (generally with id). Actually, it is multiple trees.
Condition 2 is the connection condition, where PRIOR represents the previous record, which is easy to confuse

• Connect by prior parentid = t. id indicates that the parentid of the previous record is the id of this record. That is to say, this record is the parent node of the previous record. It indicates that all nodes found with condition 1 are parent nodes;
• Connect by priorid = t. parentid indicates that the id of the previous record is the parentid of this record. That is to say, this record is a subnode of the upper level. It indicates that condition 1 is the node and all the detected nodes are subnodes.

Condition 3 is a filtering condition used to filter all returned records.

This sentence solves my problem!

First, I first find the id of the valid Integral Data in another table, and place the data from the tree table in a list by id, and then traverse the list, use the SQL statement to find all parent nodes:

select * from tableName t where  t.id != :itemId start with t.id = :itemId connect by prior parentid = t.id;

There is a condition in it:

t.id != :itemId

This is to remove itself from the node and prevent the detected data from containing its own node.
The retrieved data is placed in the parentList and then traversed. The id is used to query whether the corresponding data in the other table is valid points. If not, the next table is traversed. Otherwise, an exception is thrown, indicating that the verification failed!

Then use the SQL statement to find all the subnodes:

<span style="font-family:Microsoft YaHei;font-size:14px;">select * from tableName t where  t.id != :itemId start with t.id = :itemId connect by prior <span style="line-height: 16.8999996185303px;">id</span> = t.<span style="line-height: 16.8999996185303px;">parentid</span>;</span>

Use the Traversal method for verification.

For example, if 10 is used as the node, the system first checks all the parent nodes for verification, and then finds all child nodes for verification.

The benefit of tree query is that all parent nodes or child nodes are detected at one time.

Such an SQL statement solves a lot of problems and won't make any mistakes in the middle of the process. After development, you still operate on the database. It depends on whether you can hold it !!!

By the way, here I just use the tree to query the simplest application. If you want to learn more, I will introduce a blog post:

Http://www.blogjava.net/hwpok/archive/2010/04/07/317649.html

The lecture is more detailed. Of course, du Niang has more!