帆软BI目录

数据导入ORACLE库 写法

SELECT *
FROM (SELECT  a.id ,a.expandType,a.parentId,a.displayName,a.sortIndex,LEVEL lv ,replace(sys_connect_by_path(displayName,'//'),'//Dec-Entry_Management//','') AS 路径FROM FINE_AUTHORITY_OBJECT aSTART WITH a.id = 'decision-directory-root'CONNECT BY PRIOR a.id = a.parentId  AND a.id NOT IN('0902daf9-b8b7-435f-a38f-0310df7897f8','2f6022f3-efeb-481d-b673-fcc29f634047')/*剔除FineBI官方demo、培训习题*/
) a
WHERE a.id NOT IN ('decision-directory-root') /*总目录剔除*/
--AND a.expandType <>'3'
ORDER BY 路径 

数据导入mysql库写法

WITH RECURSIVE ra AS(SELECT a.id,a.expandType,a.parentId,a.deviceType,a.displayName,a.sortIndex,a.displayName AS path,if(a.sortIndex<=9,concat("0",CONVERT(a.sortIndex,CHAR)),CONVERT(a.sortIndex,CHAR)) AS sortIndex_listFROM FINE_AUTHORITY_OBJECT AS aWHERE a.displayName = 'Amazon'UNION ALLSELECT a.id,a.expandType,a.parentId,a.deviceType,a.displayName,a.sortIndex,concat(ra.path,'/',a.displayName) AS path,concat(ra.sortIndex_list,'-',if(a.sortIndex<=9,concat("0",CONVERT(a.sortIndex,CHAR)),CONVERT(a.sortIndex,CHAR))) AS sortIndex_listFROM FINE_AUTHORITY_OBJECT AS aINNER JOIN ra ON a.parentId = ra.idWHERE a.DEVICETYPE <> 0 
)
SELECT ra.id,ra.parentId,if(LENGTH(ra.path)-LENGTH(REPLACE(ra.path,'/',''))=1,SUBSTRING_INDEX(ra.path,'/',1),substring_index(SUBSTRING_INDEX(ra.path,'/',2),'/',-1))AS 所属主题,ra.displayName AS 报表名称,CASE ra.deviceTypeWHEN 1 THEN 'PC'WHEN 2 THEN '平板'WHEN 3 THEN 'PC、平板'WHEN 4 THEN '手机'WHEN 5 THEN 'PC、手机'WHEN 6 THEN '平板、手机'WHEN 7 THEN 'PC、平板、手机'END AS 展示终端,ra.path AS 报表路径
FROM ra 
WHERE ra.expandType = 102 # FineReport 报表
${if(len(index_filter)==0,""," and ra.id in ("+"'"+treelayer(index_filter,true,"\',\'")+"'"+")")}
ORDER BY sortIndex_list
;

ps:仅供参考，需要自己调整相应字段适配