with uCte as

(

select a.id,a.displayName,convert(nvarchar(100),'0' )as parentid,sortIndex from finedb.dbo.fine_authority_object a

where displayName in ('项目管理') --目录名称

union all

select k.id,k.displayName,convert(nvarchar(100),k.parentid),k.sortIndex from finedb.dbo.fine_authority_object k

inner join uCte c on k.parentid = c.id and k.path is null

)

select d.name as 角色,b.displayName as 报表名称

from fine_authority c

left join uCte a on c.authorityEntityId=a.id 

join finedb.dbo.fine_authority_object b on a.id=b.parentId

left join FINE_CUSTOM_ROLE d on c.roleId=d.id

where 1=1

and c.roleType='2' and c.authority=2

通过递归的方式，可以实现此要求。