【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