项目中菜单按照层级展示sql

效果如图：

直接上脚本

查四级菜单
select EFT_FLAG,MENU_ID,
CASE LEN(MENU_LVL)WHEN '4'THEN MENU_NAME ELSE '-' END AS   'MENU_NAME1',
CASE LEN(MENU_LVL)WHEN '8'THEN  MENU_NAME ELSE '-' END AS   'MENU_NAME2',
CASE LEN(MENU_LVL)WHEN '12'THEN MENU_NAME ELSE '-' END AS   'MENU_NAME3',
CASE LEN(MENU_LVL)WHEN '16'THEN MENU_NAME  ELSE '-' END AS   'MENU_NAME4',
* from  YGT..UPM_MENU  where  MENU_PUR='4' 


--查询被授权的菜单列表
--按层级查询所有上级菜单
--SELECT * FROM (
SELECT 
a.MENU_ID as 菜单系统编号,
a.MENU_NAME as 菜单系统名称,
e.DICT_ITEM_NAME as 菜单范围,
case 
  when a.PAR_MENU=0 then a.MENU_NAME 
  else 
    case 
      when not exists(select 1 from UPM_MENU where b.MENU_ID=a.PAR_MENU) then a.MENU_NAME
      when b.PAR_MENU=0 then b.MENU_NAME 
      else 
        case
          when not exists(select 1 from UPM_MENU where c.MENU_ID=b.PAR_MENU) then b.MENU_NAME
          when c.PAR_MENU=0 then c.MENU_NAME 
          else d.MENU_NAME
        end
    end
end as 一级菜单,
case 
  when a.PAR_MENU=0 then null 
  when b.PAR_MENU=0 then a.MENU_NAME 
  else 
    case
      when not exists(select 1 from UPM_MENU where c.MENU_ID=b.PAR_MENU) then a.MENU_NAME
      when c.PAR_MENU=0 then b.MENU_NAME 
      else c.MENU_NAME
    end
end as 二级菜单,
case 
  when a.PAR_MENU=0 then null 
  when b.PAR_MENU=0 then null 
  when c.PAR_MENU=0 then a.MENU_NAME 
  else b.MENU_NAME
end as 三级菜单,
case 
  when a.PAR_MENU=0 then null 
  when b.PAR_MENU=0 then null 
  when c.PAR_MENU=0 then null 
  else a.MENU_NAME
end as 四级菜单
,a.BUSI_CODE as 流程代码
FROM UPM_MENU a 
LEFT JOIN UPM_MENU b ON a.PAR_MENU=b.MENU_ID
LEFT JOIN UPM_MENU c ON b.PAR_MENU=c.MENU_ID
LEFT JOIN UPM_MENU d ON c.PAR_MENU=d.MENU_ID,
UPM_DICT_ITEMS e
where a.MENU_PUR=e.DICT_ITEM and e.DICT_CODE='MENU_PUR'
and a.MENU_ID in(
--被授权的菜单列表(受理、审核平台)
SELECT MENU_ID FROM UUM_OBJ_PERM WHERE OPP_OBJ_TYPE =2 AND OPP_OBJ_CODE IN (SELECT OPP_OBJ_CODE FROM UUM_USER_PLAT WHERE PLAT_CODE IN('2','3'))
)
--过滤为临柜、非临柜菜单、查询菜单
--AND a.MENU_PUR IN('2','3','6')
order by a.MENU_PUR asc,a.MENU_ID asc