DB2 recursive query

DB2 recursive query recursive SQL is implemented through the Common Table Expression (CTE, Common Table Expression) in DB2. Recursive SQL statements consist of recursive CTE and query of recursive CTE results. So what is recursive CTE? In short, if the FULLSELECT clause in the CTE references the CTE itself, it is a recursive CTE. Recursive CTE consists of the following three parts: the initial query of the initial query is the part of the CTE that queries the basic table. The first FULLSELECT in the CTE definition must not include the application to the CTE itself, that is, the initial query. Recursive query refers to the query of recursive logic by referencing the CTE itself. Recursive query must follow the following rules: recursive query and initial query results must contain the same number of data columns; recursive query and initial query results must have the same data column and length; recursive queries cannot contain group by or HAVING clauses, recursive queries cannot contain Outer Join, recursive queries cannot contain subqueries, and recursive queries must be joined by union all. The termination condition is usually implicit. That is, if the result set returned by the previous recursive query is null, the recursion is terminated. However, you can also set the termination condition in the recursive query, for example, limit the depth of recursive queries. If there is a table:

Java code create table node (id integer not null, PARENT_ID integer not null );

 

Query SQL:

Java code WITH temp (id, parent_id, <span style = "color: # 0000ff;"> level </span>) AS (SELECT id, parent_id, 0 FROM node WHERE <span style = "color: # ff0000;"> parent_id = 0 </span> union all select B. id, B. parent_id, <span style = "color: # 0000ff;">. level + 1 </span> FROM temp a, node B WHERE B. id =. parent_id) SELECT * FROM temp;