DB2 recursion for string grouping and Connection

DB2 recursively implements string grouping and connection operations. Recursive queries in db2 are implemented using the with clause, also known as the public expression. The public expression is in the form of the with clause at the beginning of the select statement, you can use it multiple times in a query using a public expression, and the public expression can also be connected to itself by taking an alias, so that the purpose of the loop can be achieved. Recursive queries usually have three parts to be defined: 1. A virtual table in the form of a public expression. 2. An initialization table. 3. A secondary table that is completely inner-connected to a virtual table. UNION all is used to merge the preceding three queries, and select is used to obtain the final result from recursive output. In general, the following form is with XX (x1, x2, x3) as ------- @ 0 (select. s,. s1 from a ---- @ 1 union all ---- @ 2 select * from a, xx where. s = xx. x1 ------ @ 3) select... from xx where .... ------- @ 4 @ 0: it is the with body, that is, virtual Table @ 1: It is the initialization table. here we need to define some initialized rows, that is, the starting point of your recursion, or the parent row, this part of the logic is executed only once, and its result serves as the initialization content of the virtual table recursion. @ 2: UNION all @ 3 must be used here: recursive conditions (Auxiliary tables) must be defined here, and recursive logic must be defined here, note that when the parent and child rows are connected, the logic must be clear about the parent-child relationship. Otherwise, it will easily become an endless loop. Here we will first query the results of the initialized table as conditions, after adding the execution result to the virtual table, the next recursive loop is performed as long as the records can be queried here. @ 4: Here is the query statement for the virtual table. Requirement: a real-time table. A record exists everywhere. Example: Zhang San Shanghai Zhang San Hangzhou ..... requirement: summarize by person name, splice his or her location, and separate data with '#' in the middle. Prepare: -- create table: create table Recursive_Test (User_Name VARCHAR (12 ), city VARCHAR (12); -- INSERT data INTO Recursive_Test (User_Name, City) values ('zhang san', 'hangzhou'), ('zhang san', 'zhengzhou '), ('Li si', 'hangzhou'), ('zhang san', 'nanchang '), ('Li si', 'guangzhou'), ('wang wu', 'beijing '); ----- Recursive Implementation: WITH Recursive_Test_Par (User_Name, City, Rk_Num) as (SELECT User_Name, City, ROW_NUMBER () OVER (partition by User_Name) -- grouping, generation sequence, self-Association: FROM Recursive_Test), City_Join (User_Name, City, R_Num) as (SELECT User_Name, CAST (City as varchar (100 )), rk_Num from Recursive_Test_Par WHERE Rk_Num = 1 union allselect a1.User _ Name, CAST (a1.City ||' # '| b1.City as varchar (100), a1.R _ Num + 1 from City_Join a1, recursive_Test_Par b1WHERE a1.User _ Name = b1.User _ Name and a1.R _ Num = b1.Rk _ Num-1) SELECT. user_name,. city FROM City_Join a inner join (SELECT User_Name, max (R_Num) R_Num from City_Join group by User_Name) bON. user_name = B. user_name and. r_Num = B. r_Num; -- result: Wang Wu, Beijing, Li Si, Hangzhou # Guangzhou, Zhang San, Hangzhou # Zhengzhou # Nanchang