如圖
源資料
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
uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
中間一層關系放空的話這種方式是搞不定的uj5u.com熱心網友回復：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/99627.html
標籤：MySQL
上一篇：求教各位大神！