帆软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 a
	START 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_list
	FROM FINE_AUTHORITY_OBJECT AS a
	WHERE a.displayName = 'Amazon'
	UNION ALL
	SELECT 
		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_list
	FROM FINE_AUTHORITY_OBJECT AS a
	INNER JOIN ra ON a.parentId = ra.id
	WHERE 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.deviceType
		WHEN 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:仅供参考，需要自己调整相应字段适配