finereport10.0目录sql,数据源finedb
select distinct
case
when b.displayName is null then a.displayName
when c.displayName is null then concat(a.displayName,'/',b.displayName)
when d.displayName is null then concat(a.displayName,'/',b.displayName,'/',c.displayName)
when e.displayName is null then concat(a.displayName,'/',b.displayName,'/',c.displayName,'/',d.displayName)
else concat(a.displayName,'/',b.displayName,'/',c.displayName,'/',d.displayName,'/',e.displayName)
end 完整目录,
case when a.expandType=3 then concat(a.displayName,'文件夹') else a.displayName end as 一级目录名
,case when b.expandType=3 then concat(b.displayName,'文件夹') else b.displayName end as 二级目录名
,case when c.expandType=3 then concat(c.displayName,'文件夹') else c.displayName end as 三级目录名
,case when d.expandType=3 then concat(d.displayName,'文件夹') else d.displayName end as 四级目录名
,case when e.expandType=3 then concat(e.displayName,'文件夹') else e.displayName end as 五级目录名
,concat(ifnull(a.path,''),ifnull(b.path,''),ifnull(c.path,''),ifnull(d.path,''),ifnull(e.path,'')) 文件位置
,f.realname 一级目录用户
,g.realname 二级目录用户
,h.realname 三级目录用户
,i.realname 四级目录用户
,j.realname 五级目录用户
from fine_authority_object a
left join fine_authority_object b
on a.id=b.parentId
left join fine_authority_object c
on b.id=c.parentId
left join fine_authority_object d
on c.id=d.parentId
left join fine_authority_object e
on d.id=e.parentId
left join
(select f.authorityEntityId,h.realname from
fine_authority f
left join
fine_user_role_middle g
on f.roleid=g.roleid
left join
(select * from
fine_user a
where a.username not in (
select a.username from fine_user a
left join
fine_user_role_middle b
on a.id=b.userid
left join
fine_custom_role c
on b.roleid=c.id
)) h
on g.userid=h.id
where f.authorityType=1
) f
on a.id=f.authorityEntityId
left join
(select f.authorityEntityId,h.realname from
fine_authority f
left join
fine_user_role_middle g
on f.roleid=g.roleid
left join
(select * from
fine_user a
where a.username not in (
select a.username from fine_user a
left join
fine_user_role_middle b
on a.id=b.userid
left join
fine_custom_role c
on b.roleid=c.id
)) h
on g.userid=h.id
where f.authorityType=1
) g
on b.id=g.authorityEntityId
left join
(select f.authorityEntityId,h.realname from
fine_authority f
left join
fine_user_role_middle g
on f.roleid=g.roleid
left join
(select * from
fine_user a
where a.username not in (
select a.username from fine_user a
left join
fine_user_role_middle b
on a.id=b.userid
left join
fine_custom_role c
on b.roleid=c.id
)) h
on g.userid=h.id
where f.authorityType=1
) h
on c.id=h.authorityEntityId
left join
(select f.authorityEntityId,h.realname from
fine_authority f
left join
fine_user_role_middle g
on f.roleid=g.roleid
left join
(select * from
fine_user a
where a.username not in (
select a.username from fine_user a
left join
fine_user_role_middle b
on a.id=b.userid
left join
fine_custom_role c
on b.roleid=c.id
)) h
on g.userid=h.id
where f.authorityType=1
) i
on d.id=i.authorityEntityId
left join
(select f.authorityEntityId,h.realname from
fine_authority f
left join
fine_user_role_middle g
on f.roleid=g.roleid
left join
(select * from
fine_user a
where a.username not in (
select a.username from fine_user a
left join
fine_user_role_middle b
on a.id=b.userid
left join
fine_custom_role c
on b.roleid=c.id
)) h
on g.userid=h.id
where f.authorityType=1
) j
on e.id=j.authorityEntityId
where a.parentid='decision-directory-root'
order by a.sortIndex,b.sortIndex,c.sortIndex,d.sortIndex
1min目标场景问卷
立即参与
