机构统计

来源：互联网发布：淘宝水印logo在线制作编辑：程序博客网时间：2024/04/30 18:26

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

0 0