WITH TIME_VIEW AS
(SELECT STARTDATE, ENDDATE, STARTTIME, ENDTIME
FROM TABLE(SELECT_STARTEND('$time$'))),
ORGAN_TREE AS
(SELECT O.ID,
O.ORGAN,
O.FATHERORGANID,
SYS_CONNECT_BY_PATH(O.ID, '/') PT,
LEVEL AS LOL
FROM T_PSB_ORGAN O
START WITH O.ID = #organId#
CONNECT BY PRIOR O.ID = O.FATHERORGANID),
RYMC_VIEW AS
(SELECT T1.ID, NAME, T1.IDENTITY, T1.ORGANID, T2.ORGAN ,ssjjrq, legaldate
FROM T_PSB_RYMC_BASIC T1
JOIN ORGAN_TREE T2
ON T2.ID = T1.ORGANID
where 1= 1
),
TIELU_VIEW AS
(SELECT T2.ID
FROM T_GA_RAILWAY T1
JOIN RYMC_VIEW T2
ON T1.ZJHM = T2.IDENTITY
WHERE 1 = 1),
GROUP_RYMCID AS
(SELECT T1.ID,
NAME,
T1.IDENTITY,
T1.ORGANID,
T1.ORGAN,
NVL(T2.TEMP_COUNT, 0) TIELU_COUNT
FROM RYMC_VIEW T1
LEFT JOIN (SELECT COUNT(TT.ID) TEMP_COUNT, TT.ID
FROM TIELU_VIEW TT
GROUP BY ID) T2
ON T1.ID = T2.ID)
SELECT T3.ID,
T3.ORGAN,
NVL(SUM(ZHUSU_COUNT), 0) lgzs,
max(T3.FATHERORGANID) pid,
max(T3.lol) lol
FROM ORGAN_TREE T3, ORGAN_TREE T4
LEFT JOIN (SELECT ORGANID,
NVL(SUM(CASE
WHEN ZHUSU_COUNT > 0 THEN
1
ELSE
0
END),
0) ZHUSU_COUNT
FROM GROUP_RYMCID T1
GROUP BY T1.ORGANID) T2
ON T2.ORGANID = T4.ID
WHERE (T4.PT LIKE T3.PT || '/%' OR T4.PT = T3.PT)
GROUP BY T3.ID, T3.ORGAN