select

用户ID user_id,

用户名 user_login_id,

姓名 user_name,

角色ID role_id,

角色 role_name,

部门ID dept_id,

部门 dept_name,

roleid,

authoritytype,

权限类型  permission_type_one,

类型 permission_type_two,

名称 menu_name,

    menu_url,

-- SORTINDEX,

-- PARENTID0,

-- PARENTID1,

-- PARENTID2,

-- PARENTID3,

-- PARENTID4,

权限目录类型 permission_menu_type,

适用平台 platform

from

(

select

u.ID 用户ID,

u.USERNAME 用户名,

u.REALNAME 姓名,

rc.id 角色ID,

rc.NAME 角色 ,

rd.id 部门ID,

d.NAME 部门

from

FINE_USER u

left join FINE_USER_ROLE_MIDDLE rm on

u.id = rm.userId

left join FINE_CUSTOM_ROLE rc on

rm.ROLETYPE = 2

and rm.ROLEID = rc.id

left join FINE_DEP_ROLE rd on

rm.ROLETYPE = 1

and rm.ROLEID = rd.id

left join FINE_DEPARTMENT d on

d.id = rd.DEPARTMENTID

left join FINE_POST p on

p.id = rd.POSTID

where

1 = 1

) A

join (

select

F.roleid,

f.AUTHORITYTYPE,

case

when f.AUTHORITYTYPE = '1' then '查看权限'

when f.AUTHORITYTYPE = '2' then '授权权限'

when f.AUTHORITYTYPE = '3' then '编辑权限'

when f.AUTHORITYTYPE = '4' then '数据连接管理权限'

when f.AUTHORITYTYPE = '101' then 'FineReport 模板认证权限'

when f.AUTHORITYTYPE = '102' then 'FineReport 模板查看权限'

when f.AUTHORITYTYPE = '103' then 'FineReport 模板填报权限'

when f.AUTHORITYTYPE = '201' then '业务包使用权限'

when f.AUTHORITYTYPE = '202' then '业务包管理权限'

when f.AUTHORITYTYPE = '203' then 'BI报表导出权限'

when f.AUTHORITYTYPE = '204' then 'BI仪表板分享权限'

when f.AUTHORITYTYPE = '205' then 'BI模板认证权限'

when f.AUTHORITYTYPE = '206' then 'BI模板查看权限'

when f.AUTHORITYTYPE = '207' then 'BI模板导出权限'

when f.AUTHORITYTYPE = '208' then 'BI仪表板分享角色控制权限'

when f.AUTHORITYTYPE = '209' then 'BI仪表板分享功能权限'

when f.AUTHORITYTYPE = '210' then 'BI仪表板公共链接功能权限'

end 权限类型,

case

when f.ROLETYPE = '1' then '部门'

when f.ROLETYPE = '2' then '角色'

when f.ROLETYPE = '3' then '用户'

when f.ROLETYPE = '4' then '职务'

end 类型,

a.ID,

a.DISPLAYNAME 名称,

      a.path menu_url,

a.SORTINDEX,

a.PARENTID PARENTID0,

b.PARENTID PARENTID1,

c.PARENTID PARENTID2,

d.PARENTID PARENTID3,

e.PARENTID PARENTID4,

case

when a.EXPANDTYPE = '1' then '平台管理系统节点'

when a.EXPANDTYPE = '2' then '首页'

when a.EXPANDTYPE = '3' then '目录'

when a.EXPANDTYPE = '5' then '链接'

when a.EXPANDTYPE = '6' then '文件'

when a.EXPANDTYPE = '101' then '上报流程'

when a.EXPANDTYPE = '102' then ' FineReport报表'

when a.EXPANDTYPE = '201' then ' BI报表'

end 权限目录类型,

case

when a.DEVICETYPE = '0' then '未勾选 '

when a.DEVICETYPE = '1' then 'PC '

when a.DEVICETYPE = '2' then '平板 '

when a.DEVICETYPE = '3' then 'PC、平板 '

when a.DEVICETYPE = '4' then '手机 '

when a.DEVICETYPE = '5' then 'PC、手机 '

when a.DEVICETYPE = '6' then '平板、手机 '

when a.DEVICETYPE = '7' then 'PC 、平板、手机 '

end 适用平台,

a.SORTINDEX 排序顺序

from

FINE_AUTHORITY_OBJECT a

left join FINE_AUTHORITY_OBJECT b on

a.PARENTID = b.id

left join FINE_AUTHORITY_OBJECT c on

b.PARENTID = c.id

left join FINE_AUTHORITY_OBJECT d on

c.PARENTID = d.id

left join FINE_AUTHORITY_OBJECT e on

d.PARENTID = e.id

join FINE_AUTHORITY f on

f.AUTHORITY = 2

and (a.id = f.AUTHORITYENTITYID

or a.PARENTID = f.AUTHORITYENTITYID

or b.PARENTID = f.AUTHORITYENTITYID

or c.PARENTID = f.AUTHORITYENTITYID

or d.PARENTID = f.AUTHORITYENTITYID

or e.PARENTID = f.AUTHORITYENTITYID)

order by

a.PARENTID,

a.SORTINDEX) B on

A.用户ID = B.roleid

or A.角色ID = B.roleid

or A.部门ID = B.roleid

where

not exists (

select

1

from

FINE_AUTHORITY f1

where

f1.AUTHORITY = 1

and (B.id = f1.AUTHORITYENTITYID

or B.PARENTID0 = f1.AUTHORITYENTITYID

or B.PARENTID1 = f1.AUTHORITYENTITYID

or B.PARENTID2 = f1.AUTHORITYENTITYID

or B.PARENTID3 = f1.AUTHORITYENTITYID

or B.PARENTID4 = f1.AUTHORITYENTITYID)

and A.用户ID = f1.roleid

and B.AUTHORITYTYPE = f1.AUTHORITYTYPE)

用这个sql 有些问题，在

人员管理界面和目录权限界面都已经取消了该菜单权限，但是查询的时候还是关联出来了。一时没找到关联关系。又遇到这种问题的小伙伴吗？

在这个sql 124行的位置 改成了 

or (a.PARENTID = f.AUTHORITYENTITYID and  a.parentId !='decision-directory-root')

  or (b.PARENTID = f.AUTHORITYENTITYID and  b.parentId !='decision-directory-root')

  or (c.PARENTID = f.AUTHORITYENTITYID  and  c.parentId !='decision-directory-root')

  or (d.PARENTID = f.AUTHORITYENTITYID   and  d.parentId !='decision-directory-root')

  or (e.PARENTID = f.AUTHORITYENTITYID  and  e.parentId !='decision-directory-root')

就好了 但是不知道为什么。。。。