Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use connect by in oracle, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

1. Basic grammar

Select * from table [start with condition1] connect by [prior] id=parentid

It is generally used to find the data with parent-child relationship, that is, the data with tree structure, and the returned data can clearly distinguish the data of each layer.

Start with condition1 is used to restrict the first layer of data, or root node data; based on this data to find the second layer of data, and then use the second layer of data to find the third layer of data, and so on.

Connect by [prior] id=parentid this part is used to indicate what kind of relationship oracle uses when looking for data; for example, when looking for data in the second layer, use the id of the data in the first layer to match the parentid field recorded in the table. If this condition is true, then the data found is the second layer of data, similarly looking for the third layer and the fourth layer. Wait, wait. it's all like this.

There is another use of prior:

Select * from table [start with condition1] connect by id= [prior] parentid

This usage means to find data from the bottom up, which can be understood as looking up several points of the parent from the leaf node, and matching the id in the table record with the parentid of the first layer of data. If the match is successful, then the data found is the second layer of data; the above kind is to find the leaf node down from the parent node.

Other characteristics

The level keyword, which represents the hierarchical number in the tree structure; the first layer is the number 1, and the second layer is the number 2, increasing in turn.

The CONNECT_BY_ROOT method, which can get the value of any field in the result set of the first layer assembly point; for example, CONNECT_BY_ROOT (field name).

2. Here are two examples

2.1 find the leaf node from the root node

Select, level, CONNECT_BY_ROOT (id) from tab_test t start with t.id = 0 connect by prior t.id = t.fid

2.2 find the upper node from the leaf node

-- first, modify the position of the prior keyword select. Id, level, CONNECT_BY_ROOT (id) from tab_test t start with t.id = 4 connect by t.id = prior t.fid;-- second, the prior keyword does not change the order of the subsequent id=fid logical relations, select. Id, level, CONNECT_BY_ROOT (id) from tab_test t start with t.id = 4 connect by prior t.fid = t.id

3. Write some other uses that are commonly used

3.1 generate a digital sequence result set

Use rownum to implement sequences from 1 to 10.

Select rownum from dual connect by rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.