MYSQL递归查询？？

[嘚嗒搬运工]

如图

源数据
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

第一层 第二层 第三层 第四层
结果
001 NAME1 002 NAME2 004 NAME4 005 NAME5
001 NAME1 003 NAME3 null null 006 NAME6

[嘚嗒搬运工]

中间一层关系放空的话这种方式是搞不定的

[zjcxc]

CREATE TABLE _tt(ID int,  CODE varchar(10),  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
    	WHEN T1.LEVEL THEN T1.ID
    	WHEN T2.LEVEL THEN T2.ID
    	WHEN T3.LEVEL THEN T3.ID
    	WHEN T4.LEVEL THEN T4.ID
    END AS ID1,
    CASE 1
    	WHEN T1.LEVEL THEN T1.NAME
    	WHEN T2.LEVEL THEN T2.NAME
    	WHEN T3.LEVEL THEN T3.NAME
    	WHEN T4.LEVEL THEN T4.NAME
    END AS NAME1,
    CASE 2
    	WHEN T1.LEVEL THEN T1.ID
    	WHEN T2.LEVEL THEN T2.ID
    	WHEN T3.LEVEL THEN T3.ID
    	WHEN T4.LEVEL THEN T4.ID
    END AS ID2,
    CASE 2
    	WHEN T1.LEVEL THEN T1.NAME
    	WHEN T2.LEVEL THEN T2.NAME
    	WHEN T3.LEVEL THEN T3.NAME
    	WHEN T4.LEVEL THEN T4.NAME
    END AS NAME2,
    CASE 3
    	WHEN T1.LEVEL THEN T1.ID
    	WHEN T2.LEVEL THEN T2.ID
    	WHEN T3.LEVEL THEN T3.ID
    	WHEN T4.LEVEL THEN T4.ID
    END AS ID3,
    CASE 3
    	WHEN T1.LEVEL THEN T1.NAME
    	WHEN T2.LEVEL THEN T2.NAME
    	WHEN T3.LEVEL THEN T3.NAME
    	WHEN T4.LEVEL THEN T4.NAME
    END AS NAME3,
    CASE 4
    	WHEN T1.LEVEL THEN T1.ID
    	WHEN T2.LEVEL THEN T2.ID
    	WHEN T3.LEVEL THEN T3.ID
    	WHEN T4.LEVEL THEN T4.ID
    END AS ID4,
    CASE 4
    	WHEN T1.LEVEL THEN T1.NAME
    	WHEN T2.LEVEL THEN T2.NAME
    	WHEN T3.LEVEL THEN T3.NAME
    	WHEN T4.LEVEL THEN T4.NAME
    END AS NAME4
FROM _TT T1
    LEFT JOIN _TT T2 ON T2.PARENT_ID = T1.ID
    LEFT JOIN _TT T3 ON T3.PARENT_ID = T2.ID
    LEFT JOIN _TT T4 ON T4.PARENT_ID = T3.ID
WHERE T1.PARENT_ID = 0

[zjcxc]

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