56,677
社区成员
发帖
与我相关
我的任务
分享
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
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