MYSQL recursive query??-CodePudding

As shown in figure

The source data
ID CODE NAME LEVEL PARENT_ID
1 001 NAME1 1 0
2 002 NAME2 2 1
3 003 NAME3 2 1
4 004 NAME4 3 2
5 005 NAME5 4 4
6 006 NAME6 4 3

The first layer of the second layer 3 layer 4
The results
NAME1 NAME2 002 001 004 005 NAME5 NAME4
001 NAME1 003 NAME3 null null 006 NAME6

CodePudding user response:

 SELECT 
T1. ID AS ID1, T1. NAME AS NAME1, 
T2. ID AS ID2, T2. The NAME AS NAME2. 
T3. ID AS ID3, T3. The NAME AS NAME3, 
T4. ID AS ID4, T4. NAME AS NAME4 
The FROM TB T1 
LEFT the JOIN TB T2 ON T2. The PARENT_ID=T1. ID 
LEFT the JOIN TB T3 ON T3. The PARENT_ID=T2. ID 
LEFT the JOIN TB T4 ON T4. PARENT_ID=T3. ID 
WHERE a T1. The PARENT_ID=0

CodePudding user response:

In the middle of a relationship this way is not empty

CodePudding user response:

 CREATE TABLE _tt (ID int, CODE varchar (10), the NAME varchar (10), LEVEL int, PARENT_ID int); 
INSERT INTO _tt VALUES 
(1, '001', 'NAME1', 1, 0), 
(2, '002', 'NAME2', 2, 1), 
(3, '003', 'NAME3', 2, 1), 
(4, '004', 'NAME4' 3, 2), 
(5, '005', 'NAME5', 4, 4), 
(6, '006', 'NAME6', 4, 3); 

SELECT 
CASE 1 
The WHEN T1. LEVEL THEN T1. ID 
The WHEN T2. LEVEL THEN T2. ID 
The WHEN T3. LEVEL THEN T3. ID 
The WHEN T4. LEVEL THEN T4. ID 
END AS ID1, 
CASE 1 
The WHEN T1. LEVEL THEN T1. NAME 
The WHEN T2. LEVEL THEN T2. The NAME 
The WHEN T3. LEVEL THEN T3. The NAME 
The WHEN T4. LEVEL THEN T4. NAME 
END AS NAME1, 
CASE 2 
The WHEN T1. LEVEL THEN T1. ID 
The WHEN T2. LEVEL THEN T2. ID 
The WHEN T3. LEVEL THEN T3. ID 
The WHEN T4. LEVEL THEN T4. ID 
END AS ID2, 
CASE 2 
The WHEN T1. LEVEL THEN T1. NAME 
The WHEN T2. LEVEL THEN T2. The NAME 
The WHEN T3. LEVEL THEN T3. The NAME 
The WHEN T4. LEVEL THEN T4. NAME 
END AS NAME2. 
CASE 3 
The WHEN T1. LEVEL THEN T1. ID 
The WHEN T2. LEVEL THEN T2. ID 
The WHEN T3. LEVEL THEN T3. ID 
The WHEN T4. LEVEL THEN T4. ID 
END AS ID3, 
CASE 3 
The WHEN T1. LEVEL THEN T1. NAME 
The WHEN T2. LEVEL THEN T2. The NAME 
The WHEN T3. LEVEL THEN T3. The NAME 
The WHEN T4. LEVEL THEN T4. NAME 
END AS NAME3, 
CASE 4 
The WHEN T1. LEVEL THEN T1. ID 
The WHEN T2. LEVEL THEN T2. ID 
The WHEN T3. LEVEL THEN T3. ID 
The WHEN T4. LEVEL THEN T4. ID 
END AS ID4, 
CASE 4 
The WHEN T1. LEVEL THEN T1. NAME 
The WHEN T2. LEVEL THEN T2. The NAME 
The WHEN T3. LEVEL THEN T3. The NAME 
The WHEN T4. LEVEL THEN T4. NAME 
END AS NAME4 
The FROM _TT T1 
LEFT the JOIN _TT T2 ON T2. The PARENT_ID=T1. ID 
LEFT the JOIN _TT T3 ON T3. The PARENT_ID=T2. ID 
LEFT the JOIN _TT T4 ON T4. The PARENT_ID=T3. ID 
WHERE a T1. The PARENT_ID=0