关于finedbfine_authority_object表问题

已知图里的角色有管理报表目录下的所有报表权限，fine_authority_object表里为什么只会存一条记录。如果我查出其所有的报表该怎么做

select distinct a.username,a.realname,b.roleid,c.name ,e.parentid,e.displayname,e.path

from fine_user a

left join fine_user_role_middle b

ON a.id = b.userid

left join FINE_CUSTOM_ROLE c

on c.id = b.roleid

left join fine_authority d

ON d.roleid = b.roleid

left join FINE_AUTHORITY_OBJECT e

on e.id = d.authorityentityid

where realname = 'XX'

FineReport Mors 发布于 2021-7-22 15:00 （编辑于 2021-7-22 15:04）

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共3回答

最佳回答

ycswyw1123Lv6初级互助
发布于2021-7-22 15:08（编辑于 2021-7-22 15:09）

WITH TB AS (

SELECT

obj.ID,

obj.DISPLAYNAME,

obj.PARENTID,

obj.SORTINDEX

FROM

FINE_AUTHORITY_OBJECT obj

CONNECT BY PRIOR obj.PARENTID = obj.ID

START WITH EXISTS (SELECT 1 FROM (SELECT AUTHORITYENTITYID FROM FINE_AUTHORITY WHERE EXISTS (SELECT 1 FROM (SELECT ID FROM FINE_CUSTOM_ROLE WHERE NAME ='${rolename}') A WHERE A.ID=FINE_AUTHORITY.ROLEID) AND AUTHORITY=2) A WHERE A.AUTHORITYENTITYID=obj.ID )

UNION ALL

SELECT

obj.ID,

obj.DISPLAYNAME,

obj.PARENTID,

obj.SORTINDEX

FROM

FINE_AUTHORITY_OBJECT obj

CONNECT BY PRIOR obj.ID = obj.PARENTID

)

SELECT DISTINCT * FROM TB WHERE ID <> 'decision-directory-root' AND ID <> 'decision-homepage-root' ORDER BY SORTINDEX

举报收起评论(4)

Mors(提问者) 这是oracle的吗

2021-07-22 15:15

ycswyw1123 回复 Mors(提问者) 是的

2021-07-22 15:29

Mors(提问者) 回复 ycswyw1123 finedb不是hsql吗

2021-07-22 15:41

ycswyw1123 回复 Mors(提问者) 我这边用的外置数据库，表都迁到oracle数据库了

2021-07-22 15:44

luojian0323Lv7资深互助
发布于2021-7-22 15:03

sql放上来。你这个明显是多表关联查询的结果，

举报收起评论(2)

Mors(提问者) 已放上

2021-07-22 15:04

Mors(提问者) select distinct a.username,a.realname,b.roleid,c.name ,e.parentid,e.displayname,e.path from fine_user a left join fine_user_role_middle b ON a.id = b.userid left join FINE_CUSTOM_ROLE c on c.id = b.roleid left join fine_authority d ON d.roleid = b.roleid left join FINE_AUTHORITY_OBJECT e on e.id = d.authorityentityid where realname = \'XX\'

2021-07-22 15:07