ORACLE zookeeper query, ORACLE zookeeper

ORACLE zookeeper query, ORACLE zookeeper

ORACLE supports regular query operations by using the CTE internal audit method, and also has its own unique query method. In ORACLE, this method is called continuous data query.

This example illustrates the two query methods through a simple example.

Number of rows:

CREATE TABLE TBL_TEST( ID NUMBER,  NAME VARCHAR2(100),  PID NUMBER); /BEGININSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');END; /

Requirement: ID = all the sublinks under the 1 Link (the values are 1, 2, 4, and 5 in the table)

The method for ORACLE-specific volume tracing query is as follows:

SELECT * FROM TBL_TestSTART with id = 1 -- start row connect by prior id = pid; -- prior indicates the previous row (parent row)

CTE mode:

WITH cte(ID,NAME,PID) AS(    SELECT ID,NAME,PID    FROM TBL_TEST    WHERE ID = 1UNION ALL    SELECT b.ID,b.NAME,b.PID    FROM cte a    INNER JOIN TBL_TEST b ON a.id = b.PID)SELECT * FROM cte;

The above two methods have all found positive results.

The Start With of the first method is equivalent to the part above union all of the second method. connect by is equivalent to the part below union all.

The CTE method is portable. It is also supported by SQL SERVER and DB2.

You can refer to the following articles for more examples of the CTE method:

SQL audit validation

Checking your initial experiences

Oracle recursive query

Oracle recursive query
Use of the start by prior clause in Oracle
Connect by is used in structured queries. Its basic syntax is:
Select... from tablename start with condition 1
Connect by condition 2
Where Condition 3;

Example:
Select * from table
Start with org_id = 'hbh1_wgwpy'
Connect by prior org_id = parent_id;

Simply put, a tree structure is stored in a table. For example, a table has two fields:
Org_id and parent_id indicate the parent of each record to form a tree structure.
You can use the preceding syntax to query all records of the tree.
Where:

Condition 1 is a restriction statement for the root node. Of course, you can relax the restriction to obtain multiple root nodes. Actually, it is multiple trees.
Condition 2 is the connection condition, where PRIOR is used to represent the previous record. For example, connect by prior org_id = parent_id indicates that the org_id of the previous record is the parent_id of this record, that is, the father of this record is the previous record.
Condition 3 is a filtering condition used to filter all returned records.

A brief introduction is as follows:

When you scan a tree structure table early, you need to access each node in the tree structure. A node can only be accessed once. The steps are as follows:
Step 1: start with the root node;
Step 2: Access the node;
Step 3: Check whether the node has any child nodes that have not been accessed. If yes, switch to the left-side unaccessed subsection of the node and perform Step 2. Otherwise, execute step 4;
Step 4: If the node is the root node, the access is completed; otherwise, step 5 is executed;
Step 5: return to the parent node of the node and perform step 3.

In short, the process of scanning the entire tree structure is also the process of traversing the tree in the middle order.

1. Tree Structure Description
Data in the tree structure is stored in the table. The hierarchy between the data is the parent-child relationship. It is described by the relationship between columns in the table, such as EMPNO and MGR In the EMP table. EMPNO indicates the employee's number, MGR indicates the number of the person who leads the employee, that is, the MGR value of the child node is equal to the EMPNO value of the parent node. In each row of the table, there is a MGR representing the parent node (except the root node). The entire tree structure can be determined through the parent node of each node.
You can use the connect by and start with clause in the SELECT command to query the tree structure of the table. The command format is as follows:
SELECT...
Connect by {PRIOR column name 1 = column name 2 | column name 1 = PRIOR split name 2}
[Start with];
The connect by clause indicates that each row of data is retrieved in a hierarchical order and that the data in the table is connected to the tree structure. The PRIORY operator must be placed before one of the two columns of the connection relationship. For the parent-child relationship between nodes, the PRIOR operator represents the parent node on one side and the child node on the other side to determine whether the order of the tree structure is top-down or bottom-up. In addition to column names, you can also use column expressions in the join relationship. The start with clause is optional to identify the node used as the root node for searching the tree structure. If this clause is omitted, all rows that meet the query conditions are used as the root node.

Start with: You can specify not only one root node, but also multiple root nodes.
2. About PRIOR
The PRIOR operator is placed before and after the equal sign, which determines the query order.
When PRIOR is placed before a moderate number in the connect by clause, it is forced to search from the root node to the leaf node in sequence, that is, the parent node is directed to the child node through the tree structure, we call it a top-down approach. For example:
Connect by prior empno = MGR
... The remaining full text>

Please help me explain the recursive query in Oracle

Here is an example:
Select * from table name start with id = 1 connect by prior Pid = id
The translation of this statement should be:
Select the data with ID = 1 and perform recursive queries. The previous PID is equal to the next ID.
That is, the parent ID of your next data is equal to the Child ID of the previous record.

You can also query the parent ID at a time through the child ID.
Select * from table name start with id = 'connect by prior id = Pid of a leaf
Add: I am also learning