yzm827631(提问者)参考了一些社区帮助提供的sql ，不管咋连总有人连不出权限，您能帮看看吗
SELECT
A.userName as 账号,A.[姓名],A.[角色],A.[部门],
B.AUTHORITYTYPE as 权限类型,
B.[类型],B.displayName as 表名,
B.[权限目录类型],B.[适用平台]
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 u.userName = \'040721\'
) A
LEFT 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
left 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
)
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
where --B.displayName like \'%投入产出率%\'
A.userName =\'064860\'
1min目标场景问卷
立即参与
