【Oracle】虚表的使用

主要结构是：
WITH 表昵称 AS ( SELECT … UNION ALL SELECT … )

其中UNION ALL是合并SELECT的结果集，需要注意的是，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。
（默认地，UNION 操作符选取不同的值。如果允许合并集中存在重复的值，请使用 UNION ALL。）

WITH DISPUTE_BI AS (SELECT '0' COUNT_TYPE, X.INFO_ORG_CODE,
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '27' THEN 1 ELSE NULL END),0) NCJT_TOTAL,--农村集体土地征用、流转
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '10' THEN 1 ELSE NULL END),0) GYTD_TOTAL,-- 国有土地上房屋拆迁
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '04' THEN 1 ELSE NULL END),0) CXJS_TOTAL,--城乡建设管理
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '18' THEN 1 ELSE NULL END),0) ZYQS_TOTAL,--土地、山林、水利、资源权属
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '03' THEN 1 ELSE NULL END),0) HJBH_TOTAL,--环境保护
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 IN ('13','14','20') THEN 1 ELSE NULL END),0) CWGL_TOTAL,--村务管理
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '09' THEN 1 ELSE NULL END),0) LDSB_TOTAL,--劳动社保
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 IN ('19','24') THEN 1 ELSE NULL END),0) YLWS_TOTAL,--医疗卫生
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '02' THEN 1 ELSE NULL END),0) LLGX_TOTAL,--邻里关系
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '01' THEN 1 ELSE NULL END),0) HYJT_TOTAL,--婚姻家庭
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '26' THEN 1 ELSE NULL END),0) QT_TOTAL--其他
  FROM T_ZZ_DISPUTE_MEDIATION S,
       (SELECT *
          FROM T_ZZ_DISPUTE_MEDIATION_RES A
         WHERE A.MEDIATION_RES_ID =
               (SELECT MAX(MEDIATION_RES_ID)
                  FROM T_ZZ_DISPUTE_MEDIATION_RES B
                 WHERE A.MEDIATION_ID = B.MEDIATION_ID
                   AND A.STATUS = B.STATUS
                   AND B.STATUS = '1')
           AND A.STATUS = '1') T,
       T_DC_GRID X
 WHERE S.STATUS != '0'
   AND X.STATUS = '001'
   AND S.MEDIATION_ID = T.MEDIATION_ID(+)
   AND S.GRID_ID = X.GRID_ID
   AND S.HAPPEN_TIME >= TO_DATE('2017-01-01', 'YYYY-MM-DD')
   AND S.HAPPEN_TIME < TO_DATE('2018-01-01', 'YYYY-MM-DD')
   AND X.INFO_ORG_CODE LIKE '62%'
 GROUP BY X.INFO_ORG_CODE
 UNION ALL
SELECT '1' COUNT_TYPE, X.INFO_ORG_CODE,
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '27' AND T.IS_SUCCESS = '1' THEN 1 ELSE NULL END),0) NCJT_TOTAL,
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '10' AND T.IS_SUCCESS = '1' THEN 1 ELSE NULL END),0) GYTD_TOTAL,
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '04' AND T.IS_SUCCESS = '1' THEN 1 ELSE NULL END),0) CXJS_TOTAL,
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '18' AND T.IS_SUCCESS = '1' THEN 1 ELSE NULL END),0) ZYQS_TOTAL,
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '03' AND T.IS_SUCCESS = '1' THEN 1 ELSE NULL END),0) HJBH_TOTAL,
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 IN ('13','14','20') AND T.IS_SUCCESS = '1' THEN 1 ELSE NULL END),0) CWGL_TOTAL,
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '09' AND T.IS_SUCCESS = '1' THEN 1 ELSE NULL END),0) LDSB_TOTAL,
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 IN ('19','24') AND T.IS_SUCCESS = '1' THEN 1 ELSE NULL END),0) YLWS_TOTAL,
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '02' AND T.IS_SUCCESS = '1' THEN 1 ELSE NULL END),0) LLGX_TOTAL,
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '01' AND T.IS_SUCCESS = '1' THEN 1 ELSE NULL END),0) HYJT_TOTAL,
       NVL(COUNT(CASE WHEN S.DISPUTE_TYPE_2 = '26' AND T.IS_SUCCESS = '1' THEN 1 ELSE NULL END),0) QT_TOTAL
  FROM T_ZZ_DISPUTE_MEDIATION S,
       (SELECT *
          FROM T_ZZ_DISPUTE_MEDIATION_RES A
         WHERE A.MEDIATION_RES_ID =
               (SELECT MAX(MEDIATION_RES_ID)
                  FROM T_ZZ_DISPUTE_MEDIATION_RES B
                 WHERE A.MEDIATION_ID = B.MEDIATION_ID
                   AND A.STATUS = B.STATUS
                   AND B.STATUS = '1')
           AND A.STATUS = '1') T,
       T_DC_GRID X
 WHERE S.STATUS != '0'
   AND X.STATUS = '001'
   AND S.MEDIATION_ID = T.MEDIATION_ID(+)
   AND S.GRID_ID = X.GRID_ID
   AND S.MEDIATION_DATE >= TO_DATE('2017-01-01', 'YYYY-MM-DD')
   AND S.MEDIATION_DATE < TO_DATE('2018-01-01', 'YYYY-MM-DD')
   AND X.INFO_ORG_CODE LIKE '62%'
 GROUP BY X.INFO_ORG_CODE)

 SELECT G.GRID_NAME,
        G.GRID_ID,
        CASE WHEN  (SELECT COUNT(*) FROM T_DC_GRID T WHERE T.PARENT_GRID_ID = G.GRID_ID AND T.STATUS='001') = 0 THEN 'TRUE' ELSE 'FALSE' END IS_LEAF,
        (SELECT NVL(SUM(A.NCJT_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '0'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') NCJT_CHECK_TOTAL,
        (SELECT NVL(SUM(A.NCJT_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '1'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') NCJT_MEDI_TOTAL,
        (SELECT NVL(SUM(A.GYTD_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '0'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') GYTD_CHECK_TOTAL,
        (SELECT NVL(SUM(A.GYTD_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '1'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') GYTD_MEDI_TOTAL,
        (SELECT NVL(SUM(A.CXJS_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '0'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') CXJS_CHECK_TOTAL,
        (SELECT NVL(SUM(A.CXJS_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '1'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') CXJS_MEDI_TOTAL,
        (SELECT NVL(SUM(A.ZYQS_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '0'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') ZYQS_CHECK_TOTAL,
        (SELECT NVL(SUM(A.ZYQS_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '1'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') ZYQS_MEDI_TOTAL,
        (SELECT NVL(SUM(A.HJBH_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '0'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') HJBH_CHECK_TOTAL,
        (SELECT NVL(SUM(A.HJBH_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '1'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') HJBH_MEDI_TOTAL,
        (SELECT NVL(SUM(A.CWGL_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '0'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') CWGL_CHECK_TOTAL,
        (SELECT NVL(SUM(A.CWGL_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '1'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') CWGL_MEDI_TOTAL,
        (SELECT NVL(SUM(A.LDSB_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '0'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') LDSB_CHECK_TOTAL,
        (SELECT NVL(SUM(A.LDSB_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '1'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') LDSB_MEDI_TOTAL,
        (SELECT NVL(SUM(A.YLWS_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '0'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') YLWS_CHECK_TOTAL,
        (SELECT NVL(SUM(A.YLWS_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '1'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') YLWS_MEDI_TOTAL,
        (SELECT NVL(SUM(A.LLGX_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '0'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') LLGX_CHECK_TOTAL,
        (SELECT NVL(SUM(A.LLGX_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '1'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') LLGX_MEDI_TOTAL,
        (SELECT NVL(SUM(A.HYJT_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '0'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') HYJT_CHECK_TOTAL,
        (SELECT NVL(SUM(A.HYJT_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '1'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') HYJT_MEDI_TOTAL,
        (SELECT NVL(SUM(A.QT_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '0'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') QT_CHECK_TOTAL,
        (SELECT NVL(SUM(A.QT_TOTAL), 0)
           FROM DISPUTE_BI A
          WHERE A.COUNT_TYPE = '1'
            AND A.INFO_ORG_CODE LIKE G.INFO_ORG_CODE || '%') QT_MEDI_TOTAL
   FROM T_DC_GRID G
  WHERE G.STATUS = '001'
    AND (G.GRID_ID = 305259 OR G.PARENT_GRID_ID = 305259)
  ORDER BY G.INFO_ORG_CODE