参考：用户权限查询 https://help.fanruan.com/finereport/doc-view-3882.html及评论区帆软用户Ad1yed0xrc的思路。 没涉及部门，区别：union all，NOT EXISTS中一个是 A.用户ID=f1.roleid，一个是A.roleid=f1.roleid

WITH staff AS (

SELECT

u.ID 用户ID,

u.USERNAME 用户名,

u.REALNAME 姓名,

rc.ID 角色ID,

rc.NAME 角色 ,

rm.roleid 

FROM

FINEBI11.FINE_USER u

LEFT JOIN FINEBI11.FINE_USER_ROLE_MIDDLE rm ON u.ID = rm.userId

LEFT JOIN FINEBI11.FINE_CUSTOM_ROLE rc ON rm.ROLETYPE = 2 

AND rm.ROLEID = rc.ID 

WHERE

1 = 1 

AND USERNAME = 'test'

),

B AS (

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.SORTINDEX,

a.PARENTID PARENTID0,

b.PARENTID PARENTID1,

b.DISPLAYNAME AS DISPLAYNAME1,

c.PARENTID PARENTID2,

c.DISPLAYNAME AS DISPLAYNAME2,

c.SORTINDEX AS SORTINDEX2,

d.PARENTID PARENTID3,

d.DISPLAYNAME AS DISPLAYNAME3,

e.PARENTID PARENTID4,

e.DISPLAYNAME AS DISPLAYNAME4,

f.PARENTID PARENTID5,

f.DISPLAYNAME AS DISPLAYNAME5,

g.PARENTID PARENTID6,

g.DISPLAYNAME AS DISPLAYNAME6,

h.PARENTID PARENTID7,

h.DISPLAYNAME AS DISPLAYNAME7,

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 权限目录类型

from FINEBI11.FINE_AUTHORITY_OBJECT a

left join FINEBI11.FINE_AUTHORITY_OBJECT b on a.PARENTID=b.id

left join FINEBI11.FINE_AUTHORITY_OBJECT c on b.PARENTID=c.id

left join FINEBI11.FINE_AUTHORITY_OBJECT d on c.PARENTID=d.id

left join FINEBI11.FINE_AUTHORITY_OBJECT e on d.PARENTID=e.id

left join FINEBI11.FINE_AUTHORITY_OBJECT f on e.PARENTID=f.id

left join FINEBI11.FINE_AUTHORITY_OBJECT g on f.PARENTID=g.id

left join FINEBI11.FINE_AUTHORITY_OBJECT h on g.PARENTID=h.id

 join FINEBI11.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)

where AUTHORITYTYPE = '1' AND a.DEVICETYPE NOT IN ('0','4')

-- order by a.PARENTID,a.SORTINDEX

)

SELECT

* 

FROM

(

SELECT

* 

FROM

staff A

JOIN B B ON A.用户ID = B.roleid 

WHERE

1 = 1 

AND NOT EXISTS (select 1 from  FINEBI11.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  or B.PARENTID5=f1.AUTHORITYENTITYID or B.PARENTID6=f1.AUTHORITYENTITYID or B.PARENTID7=f1.AUTHORITYENTITYID) and A.用户ID=f1.roleid and B.AUTHORITYTYPE=f1.AUTHORITYTYPE)

UNION ALL

SELECT

* 

FROM

staff A

JOIN B B ON A.角色ID = B.roleid 

WHERE

1 = 1 

AND NOT EXISTS (select 1 from  FINEBI11.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  or B.PARENTID5=f1.AUTHORITYENTITYID or B.PARENTID6=f1.AUTHORITYENTITYID or B.PARENTID7=f1.AUTHORITYENTITYID) and A.roleid=f1.roleid and B.AUTHORITYTYPE=f1.AUTHORITYTYPE)

) t1