基于FR11.0 有很大的优化空间 时间问题 没有进行优化
WITH userinfor AS
(
SELECT ID, USERNAME,FULLPATHNAME
FROM (SELECT A.ID,
A.REALNAME || '(' || A.USERNAME || ')' USERNAME,
(SELECT LISTAGG(NAME, '/') WITHIN GROUP(ORDER BY LEVEL DESC)
FROM FINE_DEPARTMENT AA
WHERE PARENTID IS NOT NULL
START WITH ID = D.ID
CONNECT BY PRIOR PARENTID = ID) FULLPATHNAME,
ROW_NUMBER() OVER(PARTITION BY A.ID, A.REALNAME || '(' || A.USERNAME || ')' ORDER BY A.ID) XH
FROM FINE_USER A,
FINE_USER_ROLE_MIDDLE B,
FINE_DEP_ROLE C,
FINE_DEPARTMENT D
WHERE A.CREATIONTYPE = 2
AND B.ROLETYPE = 1
AND A.ID = B.USERID(+)
AND B.ROLEID = C.ID(+)
AND C.DEPARTMENTID = D.ID(+))
WHERE XH = 1
),
authority AS
(SELECT NVL(C.USERID, A.ROLEID) USERID,
A.ALLDEPTID,
A.ROLETYPE,
A.AUTHORITY,
B.EXPANDTYPE,
B.ID,
B.DISPLAYNAME
FROM (SELECT A.ROLEID,
A.ROLETYPE,
A.ALLDEPTID,
B.AUTHORITY,
B.AUTHORITYENTITYID
FROM (SELECT A.ID,
A.ROLEID,
NVL(D.ID, A.ROLEID) ALLROLEID,
A.ROLETYPE,
C.ALLDEPTID
FROM FINE_AUTHORITY A,
FINE_DEP_ROLE B,
(SELECT SUBSTR(SYS_CONNECT_BY_PATH(ID, '/'),
2,
DECODE(INSTR(SYS_CONNECT_BY_PATH(ID, '/'),
'/',
1,
2) - 1,
-1,
LENGTH(SYS_CONNECT_BY_PATH(ID, '/')),
INSTR(SYS_CONNECT_BY_PATH(ID, '/'),
'/',
1,
2) - 2)) ZERIOID,
ID ALLDEPTID
FROM FINE_DEPARTMENT A
WHERE PARENTID IS NOT NULL
START WITH ID = ID
CONNECT BY PRIOR PARENTID = ID) C,
FINE_DEP_ROLE D
WHERE A.ROLEID = B.ID(+)
AND B.DEPARTMENTID = C.ZERIOID(+)
AND C.ALLDEPTID = D.DEPARTMENTID(+)) A,
(SELECT ROLEID, AUTHORITY, AUTHORITYENTITYID
FROM FINE_AUTHORITY A
WHERE A.AUTHORITYENTITYTYPE = 0 --目录权限
AND A.AUTHORITYTYPE = 1 --查看权限
GROUP BY ROLEID, AUTHORITY, AUTHORITYENTITYID) B
WHERE A.ALLROLEID = B.ROLEID) A,
FINE_AUTHORITY_OBJECT B,
FINE_USER_ROLE_MIDDLE C
WHERE B.EXPANDTYPE IN ('3', '102') --目录、报表
AND A.AUTHORITYENTITYID = B.ID
AND A.ROLEID = C.ROLEID(+)
AND B.ID <> 'decision-directory-root'--根目录ID
),
authorityinfor AS
(
SELECT SUBSTR(SYS_CONNECT_BY_PATH(ID, '/'),2,decode(INSTR(SYS_CONNECT_BY_PATH(ID, '/'),'/',1,2)-1,-1,LENGTH(SYS_CONNECT_BY_PATH(ID, '/')),INSTR(SYS_CONNECT_BY_PATH(ID, '/'),'/',1,2)-2)) zeroid,
A.ID,
a.expandtype,
A.DISPLAYNAME,
(SELECT LISTAGG(DISPLAYNAME, '/') WITHIN GROUP(ORDER BY LEVEL DESC)
FROM Fine_Authority_Object aa
WHERE PARENTID IS NOT NULL
START WITH ID = A.ID
CONNECT BY PRIOR PARENTID =AA.ID) REPORTFULLPATH,
TO_CHAR(A.PATH) modelfllpath,
LEVEL
FROM Fine_Authority_Object A
WHERE PARENTID IS NOT NULL
START WITH ID = ID
CONNECT BY PARENTID = PRIOR ID
),
authoritylevel AS
(
SELECT A.ID,
A.DISPLAYNAME,
LEVEL LEVELNAME
FROM Fine_Authority_Object A
WHERE PARENTID IS NOT NULL
START WITH ID = '2eaccaa7-f149-4ba0-bf45-629abe554f30'--组织架构根节点ID
CONNECT BY PARENTID = PRIOR ID
),
deptleve AS
(
SELECT A.ID,
A.DISPLAYNAME,
LEVEL deptleve
FROM Fine_Department A
WHERE PARENTID IS NOT NULL
START WITH ID = '482267473'
CONNECT BY PARENTID = PRIOR ID
)
SELECT USERID,
USERNAME,
FULLPATHNAME,
REPORTID,
REPORTNAME,
REPORTFULLPATH,
modelfllpath
FROM
(
SELECT ID USERID,
USERNAME,
FULLPATHNAME,
REPORTID,
REPORTNAME,
REPORTFULLPATH,
modelfllpath
FROM
(
SELECT a.id,
a.USERNAME,
a.FULLPATHNAME,
b.AUTHORITY,
C.ID REPORTID,
C.DISPLAYNAME REPORTNAME,
c.REPORTFULLPATH,
D.LEVELNAME,
c.modelfllpath,
row_number()OVER(PARTITION BY a.id,c.ID ORDER BY D.LEVELNAME DESC) rn
FROM userinfor a,
authority b,
(SELECT zeroid,ID,DISPLAYNAME,REPORTFULLPATH,modelfllpath FROM authorityinfor WHERE expandtype='102' AND REPORTFULLPATH LIKE ('%国药新疆%'))c,
authoritylevel D
WHERE a.id=b.userid(+)
AND b.id=c.zeroid(+)
AND B.ID=D.ID(+)
)
WHERE RN=1
AND AUTHORITY=2
AND REPORTID IS NOT NULL
)
;
