oracle 结果集合并

SELECT case
         when u.union_type = '1' then
          (SELECT TO_CHAR(count(1))
             FROM T_UEP_DB_UNION A
            WHERE A.IS_ENABLE = '1'
              and a.union_type = '2'
            start with A.UNION_CODE = U.UNION_CODE
           CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)
         WHEN U.UNION_TYPE = '2' THEN
          U.UNION_NAME
         WHEN U.UNION_TYPE = '3' THEN
          U.PARENT_ORGANIZATION_NAME
       end JCGH,
       case
         when u.union_type = '1' OR U.UNION_TYPE = '2' then
          (SELECT TO_CHAR(count(1))
             FROM T_UEP_DB_UNION A
            WHERE A.IS_ENABLE = '1'
              and a.union_type = '3'
            start with A.UNION_CODE = U.UNION_CODE
           CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)
         WHEN U.UNION_TYPE = '3' THEN
          U.UNION_NAME
       end FH,
       (SELECT count(1)
          FROM t_uep_db_employee e
         where e.is_enable = '1'
           and e.IS_UNION_STATE = 0
           and e.union_code in
               (SELECT a.union_code
                  FROM T_UEP_DB_UNION A
                 WHERE A.IS_ENABLE = '1'
                 start with A.UNION_CODE = U.UNION_CODE
                CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) HY,
       (SELECT count(1)
          FROM t_uep_db_employee e
         where e.is_enable = '1'
           and e.employee_sex = '1'
           and e.IS_UNION_STATE = '0'
           and e.union_code in
               (SELECT a.union_code
                  FROM T_UEP_DB_UNION A
                 WHERE A.IS_ENABLE = '1'
                 start with A.UNION_CODE = U.UNION_CODE
                CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) man,
       (SELECT count(1)
          FROM t_uep_db_employee e
         where e.is_enable = '1'
           and e.employee_sex = '0'
           and e.IS_UNION_STATE = '0'
           and e.union_code in
               (SELECT a.union_code
                  FROM T_UEP_DB_UNION A
                 WHERE A.IS_ENABLE = '1'
                 start with A.UNION_CODE = U.UNION_CODE
                CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) woman,
       (SELECT count(1)
          FROM t_uep_db_employee e
         where e.is_enable = '1'
         and e.IS_UNION_STATE = '0'
           and e.employee_academic = '0'
           and e.union_code in
               (SELECT a.union_code
                  FROM T_UEP_DB_UNION A
                 WHERE A.IS_ENABLE = '1'
                 start with A.UNION_CODE = U.UNION_CODE
                CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) ZZ,
       (SELECT count(1)
          FROM t_uep_db_employee e
         where e.is_enable = '1'
         and e.IS_UNION_STATE = '0'
           and e.employee_academic = '1'
           and e.union_code in
               (SELECT a.union_code
                  FROM T_UEP_DB_UNION A
                 WHERE A.IS_ENABLE = '1'
                 start with A.UNION_CODE = U.UNION_CODE
                CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) ZK,
       (SELECT count(1)
          FROM t_uep_db_employee e
         where e.is_enable = '1'
         and e.IS_UNION_STATE = '0'
           and e.employee_academic = '2'
           and e.union_code in
               (SELECT a.union_code
                  FROM T_UEP_DB_UNION A
                 WHERE A.IS_ENABLE = '1'
                 start with A.UNION_CODE = U.UNION_CODE
                CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) BK,
       (SELECT count(1)
          FROM t_uep_db_employee e
         where e.is_enable = '1'
         and e.IS_UNION_STATE = '0'
           and e.employee_academic = '3'
           and e.union_code in
               (SELECT a.union_code
                  FROM T_UEP_DB_UNION A
                 WHERE A.IS_ENABLE = '1'
                 start with A.UNION_CODE = U.UNION_CODE
                CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) SSYJS,
       (SELECT count(1)
          FROM t_uep_db_employee e
         where e.is_enable = '1'
           and (e.employee_academic not in ('0','1', '2', '3') or e.employee_academic is null)
           and e.union_code in
               (SELECT a.union_code
                  FROM T_UEP_DB_UNION A
                 WHERE A.IS_ENABLE = '1'
                 start with A.UNION_CODE = U.UNION_CODE
                CONNECT BY PRIOR A.UNION_CODE = A.PARENT_ORGANIZATION_CODE)) QT
  FROM T_UEP_DB_UNION U
 WHERE U.UNION_CODE = '234E45F0077881AAE0430AA3034681AA'

 

转载于:https://www.cnblogs.com/xiaz/p/10531547.html