oracle with as 示例

1.情景展示

一个查询结果，要重复使用多次，也就是对查询结果进行重新筛选，如何实现？

如上图所示，对查询结果A表进行了having截取；

现在，要对A表再次筛选，并将查询结果与having结果进行合并，这个时候，我们就可以对表A进行复用。

 

2.具体分析

Oracle在9i中引入了with语句。with语句用来给查询语句中的子查询命名，随后就可以在查询语句的其他地方引用这个名称。

格式如下：

WITH <alias_name> AS (subquery_ sql statement)
SELECT <column_name_list> FROM <alias>;

3.解决方案

WITH TMP_TAB AS
 (SELECT A.ORGCODE,
         (SELECT T.ORGNAME FROM YLT_ORG_INFO T WHERE T.ORGCODE = A.ORGCODE) ORGNAME,
         (SELECT T.AREA FROM YLT_ORG_INFO T WHERE T.ORGCODE = A.ORGCODE) AREA,
         SUM(A.YC_BSL) YC_BSL,
         SUM(A.SC_BSL) SC_BSL,
         SUM(A.YC_BSL) - SUM(A.SC_BSL) AS WC_BSL
    FROM (SELECT T.ORGCODE, COUNT(1) YC_BSL, 0 SC_BSL
            FROM META_ORG_TABLE T
           GROUP BY T.ORGCODE
          UNION ALL
          SELECT T2.ORGCODE, 0 YC_BSL, COUNT(DISTINCT T3.TABLE_NAME) SC_BSL
            FROM META_ORG_TABLE T2, STANDARD_JX.ZYK_DATE_CHECK T3
           WHERE T2.ORGCODE = T3.ORGCODE(+)
             AND T2.TABLENAME = T3.TABLE_NAME(+)
             AND T3.DATA_NUM > 0
           GROUP BY T2.ORGCODE
          UNION ALL
          SELECT T2.ORGCODE, 0 YC_BSL, 0 SC_BSL
            FROM META_ORG_TABLE T2, STANDARD_JX.ZYK_DATE_CHECK T3
           WHERE T2.ORGCODE = T3.ORGCODE(+)
             AND T2.TABLENAME = T3.TABLE_NAME(+)
             AND T3.DATA_NUM <= 0
           GROUP BY T2.ORGCODE) A
   GROUP BY A.ORGCODE)
SELECT *
  FROM TMP_TAB
 WHERE ORGCODE IN
       (SELECT T.ORGCODE
          FROM (SELECT DISTINCT ORGCODE FROM META_ORG_TABLE) T,
               YLT_ORG_INFO T2
         WHERE T.ORGCODE = T2.ORGCODE(+)
           AND T2.PARENTORGID =
               (SELECT PARENTORGID FROM YLT_ORG_INFO WHERE ORGCODE = '410425'))
UNION ALL
SELECT LISTAGG(B.ORGCODE, ',') WITHIN GROUP(ORDER BY B.ORGCODE) ORGCODE,
       '基层医院' ORGNAME,
       B.AREA,
       SUM(YC_BSL) YC_BSL,
       SUM(SC_BSL) SC_BSL,
       SUM(WC_BSL) WC_BSL
  FROM TMP_TAB B
 WHERE B.AREA = '410425'
 GROUP BY B.AREA

执行结果：

使用这种方式，比原生的临时表好用得多。

4.拓展

用ibatis如何实现？

分页统计

查看代码

<select id="getCountyStatistics" parameterClass="map" resultClass="java.util.HashMap">
    <isNotNull  property="END">
    SELECT F.* FROM (
    </isNotNull>
    SELECT E.*, ROWNUM ROWNO FROM (
      WITH TMP_TAB AS
       (SELECT A.ORGCODE,
               (SELECT T.ORGNAME FROM YLT_ORG_INFO T WHERE T.ORGCODE = A.ORGCODE) ORGNAME,
               (SELECT T.AREA FROM YLT_ORG_INFO T WHERE T.ORGCODE = A.ORGCODE) AREA,
               SUM(A.YC_BSL) YC_BSL,
               SUM(A.SC_BSL) SC_BSL,
               SUM(A.YC_BSL) - SUM(A.SC_BSL) AS WC_BSL
          FROM (SELECT T.ORGCODE, COUNT(1) YC_BSL, 0 SC_BSL
                  FROM META_ORG_TABLE T
                 GROUP BY T.ORGCODE
                UNION ALL
                SELECT T2.ORGCODE, 0 YC_BSL, COUNT(DISTINCT T3.TABLE_NAME) SC_BSL
                  FROM META_ORG_TABLE T2, STANDARD_JX.ZYK_DATE_CHECK T3
                 WHERE T2.ORGCODE = T3.ORGCODE(+)
                   AND T2.TABLENAME = T3.TABLE_NAME(+)
                   <![CDATA[
                    AND T3.DATA_NUM > 0
                   ]]>
                 GROUP BY T2.ORGCODE
                UNION ALL
                SELECT T2.ORGCODE, 0 YC_BSL, 0 SC_BSL
                  FROM META_ORG_TABLE T2, STANDARD_JX.ZYK_DATE_CHECK T3
                 WHERE T2.ORGCODE = T3.ORGCODE(+)
                   AND T2.TABLENAME = T3.TABLE_NAME(+)
                   <![CDATA[
                    AND T3.DATA_NUM <= 0
                   ]]>
                 GROUP BY T2.ORGCODE) A
         GROUP BY A.ORGCODE)
      SELECT *
        FROM TMP_TAB
       WHERE ORGCODE IN
             (SELECT T.ORGCODE
                FROM (SELECT DISTINCT ORGCODE FROM META_ORG_TABLE) T,
                     YLT_ORG_INFO T2
               WHERE T.ORGCODE = T2.ORGCODE(+)
                 AND T2.PARENTORGID =
                     (SELECT PARENTORGID FROM YLT_ORG_INFO WHERE ORGCODE = #ORGCODE#))
                 <isNotEmpty prepend="and" property="ORGNAME">
                   ORGNAME LIKE '%' || #ORGNAME# || '%'
                 </isNotEmpty>    
      <isNotEmpty prepend="UNION ALL" property="AREA">
        SELECT LISTAGG(B.ORGCODE, ',') WITHIN GROUP(ORDER BY B.ORGCODE) ORGCODE,
               '基层医院' ORGNAME,
               B.AREA,
               SUM(YC_BSL) YC_BSL,
               SUM(SC_BSL) SC_BSL,
               SUM(WC_BSL) WC_BSL
          FROM TMP_TAB B
         WHERE B.AREA = #AREA#
         GROUP BY B.AREA
      </isNotEmpty>      
    )E
    <isNotNull  property="END">
        <![CDATA[ WHERE rownum <=#END# ) F WHERE F.ROWNO >=#START#]]>
    </isNotNull>
</select>

 总数查询

查看代码

 <select id="getCountyStatisticsCount" parameterClass="map" resultClass="java.lang.Integer">
    SELECT COUNT(1)
      FROM (SELECT 1 AS TEMP_NUM
              FROM (SELECT T.ORGCODE, COUNT(1) YC_BSL, 0 SC_BSL
                      FROM META_ORG_TABLE T
                     GROUP BY T.ORGCODE
                    UNION ALL
                    SELECT T2.ORGCODE,
                           0 YC_BSL,
                           COUNT(DISTINCT T3.TABLE_NAME) SC_BSL
                      FROM META_ORG_TABLE T2, STANDARD_JX.ZYK_DATE_CHECK T3
                     WHERE T2.ORGCODE = T3.ORGCODE(+)
                       AND T2.TABLENAME = T3.TABLE_NAME(+)
                       <![CDATA[
                        AND T3.DATA_NUM > 0
                       ]]>
                     GROUP BY T2.ORGCODE
                    UNION ALL
                    SELECT T2.ORGCODE, 0 YC_BSL, 0 SC_BSL
                      FROM META_ORG_TABLE T2, STANDARD_JX.ZYK_DATE_CHECK T3
                     WHERE T2.ORGCODE = T3.ORGCODE(+)
                       AND T2.TABLENAME = T3.TABLE_NAME(+)
                       <![CDATA[
                        AND T3.DATA_NUM <= 0
                       ]]>
                     GROUP BY T2.ORGCODE) A
             GROUP BY A.ORGCODE
            HAVING A.ORGCODE IN (SELECT T.ORGCODE
                                  FROM (SELECT DISTINCT ORGCODE
                                          FROM META_ORG_TABLE) T,
                                       YLT_ORG_INFO T2
                                 WHERE T.ORGCODE = T2.ORGCODE(+)
                                   AND T2.PARENTORGID =
                                       (SELECT PARENTORGID
                                          FROM YLT_ORG_INFO
                                         WHERE ORGCODE = #ORGCODE#))
                                   <isNotEmpty prepend="and" property="ORGNAME">
                                    ORGNAME LIKE '%' || #ORGNAME# || '%'
                                   </isNotEmpty>
            <isNotEmpty prepend="UNION ALL" property="AREA">
            SELECT 0 AS TEMP_NUM FROM DUAL
            </isNotEmpty>
              )
</select>

 

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

 相关推荐：

• 个人主页
• ORACLE-临时表 
• Oracle WM_CONCAT()的替代方案
• IBATIS + ORACLE（二）