WITH COMP_FUNC AS
(SELECT T.FID FID
FROM DPCRM.T_AUTH_FUNCTION T
START WITH T.FFUNCTIONCODE = 01006
CONNECT BY PRIOR T.FFUNCTIONCODE = T.FPARENTCODE),
COMP_ROLE AS
(SELECT DISTINCT (N.FROLEID) FID
FROM DPCRM.T_AUTH_FUNCTIONROLE N
WHERE N.FFUNCID IN (SELECT TT.FID FROM COMP_FUNC TT)),
COM_NUM AS
(SELECT R1.FROLEID RID, COUNT(*) CO
FROM COMP_FUNC CF
INNER JOIN DPCRM.T_AUTH_FUNCTIONROLE R1
ON R1.FFUNCID = CF.FID
GROUP BY R1.FROLEID),
ALL_NUM AS
(SELECT R1.FROLEID RID, COUNT(*)CO
FROM COMP_ROLE CR
INNER JOIN DPCRM.T_AUTH_FUNCTIONROLE R1
ON R1.FROLEID = CR.FID
GROUP BY R1.FROLEID)
SELECT A.*,C.* FROM ALL_NUM A INNER JOIN COM_NUM C
ON A.RID=C.RID WHERE A.CO<>C.CO;
(SELECT T.FID FID
FROM DPCRM.T_AUTH_FUNCTION T
START WITH T.FFUNCTIONCODE = 01006
CONNECT BY PRIOR T.FFUNCTIONCODE = T.FPARENTCODE),
COMP_ROLE AS
(SELECT DISTINCT (N.FROLEID) FID
FROM DPCRM.T_AUTH_FUNCTIONROLE N
WHERE N.FFUNCID IN (SELECT TT.FID FROM COMP_FUNC TT)),
COM_NUM AS
(SELECT R1.FROLEID RID, COUNT(*) CO
FROM COMP_FUNC CF
INNER JOIN DPCRM.T_AUTH_FUNCTIONROLE R1
ON R1.FFUNCID = CF.FID
GROUP BY R1.FROLEID),
ALL_NUM AS
(SELECT R1.FROLEID RID, COUNT(*)CO
FROM COMP_ROLE CR
INNER JOIN DPCRM.T_AUTH_FUNCTIONROLE R1
ON R1.FROLEID = CR.FID
GROUP BY R1.FROLEID)
SELECT A.*,C.* FROM ALL_NUM A INNER JOIN COM_NUM C
ON A.RID=C.RID WHERE A.CO<>C.CO;