SQL 遞迴排序層級表

sql 遞迴 cte

csben983049 2022-06-24 12:13:48 ‧ 1246 瀏覽

分享至

請問各位大大~
今天有一個選單表長這樣

選單代碼    選單名稱   選單層級  上層選單
MENU_CODE  MENU_NAME  DEPTHS 
1.    M0         系統        1    NULL
2.    M00        權限        2    M0
3.    M00-00    角色權限     3     M00
4.    M00-01    角色審核     3     M00

需要用遞迴的方式，產出類似以下的表

   選單 名稱  選單  名稱   選單    名稱
1.  M0  系統  M00  權限
2.  M0  系統  M00  權限  M00-00 角色權限
3.  M0  系統  M00  權限  M00-01 角色審核

想破腦袋了，寫不出來，請各位大大幫助
補充:我的版本可以用CTE

石頭 iT邦高手 1 級 ‧ 2022-06-24 12:44:54 檢舉

請提供你使用 RDBMS 和版本

石頭 iT邦高手 1 級 ‧ 2022-06-24 12:46:07 檢舉

如果你的 RDBMS 支援 CTE 遞迴答案是 CTE 遞迴

阿里巴巴大俠 iT邦新手 3 級 ‧ 2022-06-24 13:29:39 檢舉

你的選單table有"上層選單代碼"的欄位嗎?

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

一級屠豬士

iT邦大師 1 級 ‧ 2022-06-26 09:19:53

create table it220626 (
  id int not null primary key
, code text not null
, name text not null
, parent int
);

insert into it220626 values
(1, 'M0', '系統', NULL),
(2, 'M00', '權限', 1),
(3, 'M00-00', '角色權限', 2),
(4, 'M00-01', '角色審核', 2);

with recursive c as (
select id
     , '[' || code || ',' || name || ']' as path
  from it220626
 where parent is null
union all
select i.id
     , c.path || ' -> ' || '[' || i.code || ',' || i.name || ']'
  from it220626 i
  join c
    on i.parent = c.id
)
select id
     , path
  from c;
  
 id |                     path                     
----+----------------------------------------------
  1 | [M0,系統]
  2 | [M0,系統] -> [M00,權限]
  3 | [M0,系統] -> [M00,權限] -> [M00-00,角色權限]
  4 | [M0,系統] -> [M00,權限] -> [M00-01,角色審核]
(4 rows)

Demo

回應 1
分享
檢舉

lsc12178 iT邦新手 5 級 ‧ 2022-06-27 11:37:25 檢舉

只有三層的話，不用遞廻
create table it220626 (
id int not null primary key
, code text not null
, name text not null
, parent int
);

insert into it220626 values
(1, 'M0', '系統', NULL),
(2, 'M00', '權限', 1),
(3, 'M00-00', '角色權限', 2),
(4, 'M00-01', '角色審核', 2);

Select
(case When i2.parent Is Null Then i2.code Else i1.code End) as 選單1
, (case When i2.parent Is Null Then i2.name Else i1.name End) as 名稱1
, (Case When i2.parent Is Null Then i3.code Else i2.code End) as 選單2
, (Case When i2.parent Is Null Then i3.name Else i2.name End) as 名稱2
, (Case When i2.parent Is Null Then '' Else i3.code End) as 選單3
, (Case When i2.parent Is Null Then '' Else i3.name End) as 名稱3
--, i1.parent as p1, i2.parent as p2, i3.parent as p3
From it220626 i3
Inner Join it220626 i2 On i3.parent = i2.id
Left Outer Join it220626 i1 On i2.parent = i1.id
Order by i1.parent, i2.parent, i3.parent