oracle学习之with as

java端 mybatis

with as其实就是一个子查询

为什么要用WITH？

1. 如果需要在一段复杂查询里多次应用同一个查询，用WITH可实现代码重用；

2. WITH查询类似将查询结果保留到用户临时表里，在大的复杂查询中可以减少IO，有一定的性能优化作用。


WITH查询有何限制与特性？
4. 如果当前schema下有与WITH查询别名相同的表，查询中WITH查询生成的表优先；

5. 只能用于select 语句；

6. WITH可包含一个或多个查询；

7. WITH查询可被其它查询或WITH查询引用。

视图

 <select id="getBill" resultMap="BaseResultMap" parameterType="java.util.Map">
        WITH
        --基础数据，不分批次进行压缩
        T_CARRY_OVER AS(
            SELECT DISTINCT
            A.ID AS ID
            , A.MONTH AS MONTH
            , C.CHARGE_ITEM_ID AS CHARGE_ITEM_ID
            , B.ITEM_ID AS ITEM_ID
            FROM YKGL.WH_CARRY_OVER A
            , YKGL.WH_STOCK_CARRY_OVER B
            , YKGL.WH_ITEM C
            WHERE A.MONTH >= #{startTime}
            AND A.MONTH &lt; #{endTime}
            AND B.CARRY_OVER_ID = A.ID
            AND B.ITEM_ID = C.ID
        ),
        --进货
        T_IN AS(
            SELECT
            C.ITEM_ID AS ITEM_ID
            , SUM(C.QUANTITY) AS IN_QUANTITY
            , SUM(C.PURCHASE_AMOUNT) AS IN_PURCHASE_AMOUNT
            , SUM(C.RETAIL_AMOUNT) AS IN_RETAIL_AMOUNT
            FROM YKGL.WH_IN B
            , YKGL.WH_IN_DETAIL C
            WHERE B.CARRY_OVER_ID IN ( SELECT ID FROM T_CARRY_OVER )
            AND B.STATUS = 1
            AND C.IN_ID = B.ID
            GROUP BY C.ITEM_ID
        ),
        --出库
        T_OUT AS (
            SELECT
            C.ITEM_ID AS ITEM_ID
            , SUM(C.QUANTITY) AS OUT_QUANTITY
            , SUM(C.PURCHASE_AMOUNT) AS OUT_PURCHASE_AMOUNT
            , SUM(C.RETAIL_AMOUNT) AS OUT_RETAIL_AMOUNT
            FROM YKGL.WH_OUT B
            , YKGL.WH_OUT_DETAIL C
            WHERE B.CARRY_OVER_ID IN ( SELECT ID FROM T_CARRY_OVER )
            AND B.STATUS = 1
            AND C.OUT_ID = B.ID
            GROUP BY C.ITEM_ID
        ),
        --盘点
        T_CHECK AS (
            SELECT
            C.ITEM_ID
            , SUM(C.DIFF_QTY) AS NOW_DIFF_QTY
            , SUM(C.DIFF_PUR_AMT) AS DIFF_PUR_AMT
            , SUM(C.DIFF_RET_AMT) AS DIFF_RET_AMT
            FROM YKGL.WH_STOCK_CHECK B
            , YKGL.WH_STOCK_CHECK_DETAIL C
            WHERE B.CARRY_OVER_ID IN ( SELECT ID FROM T_CARRY_OVER )
            AND C.CHECK_ID = B.ID
            GROUP BY C.ITEM_ID
        ),
        --上期结余
        T_LAST AS (
            SELECT DISTINCT
            B.ITEM_ID AS ITEM_ID
            , SUM(B.QUANTITY) AS LAST_QUANTITY
            , SUM(B.PURCHASE_AMOUNT) AS LAST_PURCHASE_AMOUNT
            , SUM(RETAIL_AMOUNT) AS LAST_RETAIL_AMOUNT
            FROM YKGL.WH_CARRY_OVER A
            , YKGL.WH_STOCK_CARRY_OVER B
            WHERE A.MONTH = #{lastTime}
            AND B.CARRY_OVER_ID = A.ID
            GROUP BY B.ITEM_ID
        )
        SELECT
        T_CARRY_OVER.ITEM_ID
        , T_CARRY_OVER.MONTH
        , T_ITEM.NAME AS CHARGE_ITEM_NAME
        , T_ITEM.CODE AS CHARGE_ITEM_CODE
        , T_ITEM.UNIT
        , T_ITEM.SPEC
        , T_IN.IN_QUANTITY
        , T_IN.IN_PURCHASE_AMOUNT
        , T_IN.IN_RETAIL_AMOUNT
        , T_OUT.OUT_QUANTITY
        , T_OUT.OUT_PURCHASE_AMOUNT
        , T_OUT.OUT_RETAIL_AMOUNT
        , T_CHECK.NOW_DIFF_QTY
        , T_CHECK.DIFF_PUR_AMT
        , T_CHECK.DIFF_RET_AMT
        , T_LAST.LAST_QUANTITY
        , T_LAST.LAST_PURCHASE_AMOUNT
        , T_LAST.LAST_RETAIL_AMOUNT
        FROM T_CARRY_OVER
        LEFT OUTER JOIN HISBASE.CHARGE_ITEM T_ITEM ON T_ITEM.ID = T_CARRY_OVER.CHARGE_ITEM_ID AND T_ITEM.IS_HISTORY = 0
        LEFT OUTER JOIN T_IN ON T_IN.ITEM_ID = T_CARRY_OVER.ITEM_ID
        LEFT OUTER JOIN T_OUT ON T_OUT.ITEM_ID = T_CARRY_OVER.ITEM_ID
        LEFT OUTER JOIN T_CHECK ON T_CHECK.ITEM_ID = T_CARRY_OVER.ITEM_ID
        LEFT OUTER JOIN T_LAST ON T_LAST.ITEM_ID = T_CARRY_OVER.ITEM_ID

    </select>