oracle虚表能加筛选条件吗,【Oracle】虚表的使用

【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

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！