SELECT * 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 ${if(len(fine_username) == 0,"","and u.USERNAME ='"+fine_username+"' ") }

) A

JOIN (

select

F.roleid,

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 AUTHORITYTYPE,

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,

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)

where 

not exists (select 1 from  FINE_AUTHORITY f1 where f1.AUTHORITY=1 and (a.id=f1.AUTHORITYENTITYID or a.PARENTID=f1.AUTHORITYENTITYID or b.PARENTID=f1.AUTHORITYENTITYID or c.PARENTID=f1.AUTHORITYENTITYID or d.PARENTID=f1.AUTHORITYENTITYID or e.PARENTID=f1.AUTHORITYENTITYID) and f.roleid=f1.roleid and f.AUTHORITYTYPE=f1.AUTHORITYTYPE)

-- and  f.roleid='old-platform-custom-1' 

order by a.PARENTID,a.SORTINDEX) B ON A.用户ID=B.roleid OR A.角色ID=B.roleid   OR A.部门ID=B.roleid

 某个大神写的

目前我测试过插件与上面的sql。以及自己按文档编写的sql语句，反正都有差异。每一个完全正确的结果！！不管是在FR10和FR11里面，都有很多差异

在权限管理里可以直接看呀。

权限导出插件-https://help.fanruan.com/finereport/doc-view-3508.html

select  fu.userName '用户名',fu.realName '姓名',

fd.name '部门',fp.name '职位',faa.displayName '报表',faa.`path`  '路径'

,faa.subject AS  '主题', faa.id  from 

(

-- 按报表名称开权限

select  fao.displayName ,fao.`path`  

,SUBSTRING_INDEX(fao.`path` , "/", 1) AS  'subject', fao.id,fa.roleId  

from  finedb11.fine_authority fa

left join finedb11.fine_authority_object  fao on  fao.id=fa.authorityEntityId 

where fa.authorityEntityType=0 -- 0目录权限

and fao.`path` is   not null 

and fa.authorityType =1 -- 查看目录

and fa.authority=2 -- 权限 2：允许 1:拒绝

 -- and fao.`path`like '%'

 union all 

 -- 按目录开的，剔除按目录和按报表名称已经关闭的；

 select  fao.displayName ,fao.`path`  

,SUBSTRING_INDEX(fao.`path` , "/", 1) AS  'subject', fao.id,fa.roleId  

from  finedb11.fine_authority fa

left join finedb11.fine_authority_object  fao on   locate(fa.authorityEntityId, fao.fullPath  )>0 

where fa.authorityEntityType=0 

-- 0目录权限

and fao.`path` is   not null 

and fa.authorityType =1

and fa.authority=2

 -- and fao.`path` like ''

-- 剔除按目录和按报表名称已经关闭的；

 and  not exists  

  (select 1  from  fine_authority f1 where f1.AUTHORITY=1  and (fao.id=f1.authorityEntityId or  locate(f1.authorityEntityId, fao.fullPath  )>0  )  and fa.roleid=f1.roleid 

 )

) faa

 left join finedb11.fine_dep_role fdr on fdr.id=faa.roleId 

left join finedb11.fine_post fp  on fdr.postId =fp.id

left join finedb11.fine_department fd on fdr.departmentId =fd.id 

left join finedb11.fine_user_role_middle furm  on fdr.id =furm.roleId 

left join finedb11.fine_user fu  on furm.userId =fu.id