SQL優化：with as 作為union all的子查詢，避免多次掃描表

原文連結：

https://blog.csdn.net/hansplay/article/details/81945415?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.channel_param

文法:

1.結構: with 别名 as ( 公共sql片段 )

2.CTE(公共表達式)後面必須直接緊跟使用CTE的SQL語句，否則失效;

如:

1. with cr as (select CountryRegionCode from person.CountryRegion where Name like ‘C%’ )

select * from person.StateProvince where CountryRegionCode in (select * from cr) 有效

1. with cr as (select CountryRegionCode from person.CountryRegion where Name like ‘C%’ )

select * from person.StateProvince

select * from person.StateProvince where CountryRegionCode in (select * from cr) 無效

3.CTE後面也可以跟其他的CTE，但隻能使用一個with，多個CTE中間用逗号（,）分隔,如:

with

cte1 as (select * from table1 where name like ‘abc%’),

cte2 as (select * from table2 where id > 20),

cte3 as ( select * from table3 where price < 100)

select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id

4.如果CTE的表達式名稱與某個資料表或視圖重名，則緊跟在該CTE後面的SQL語句使用的仍然是CTE，當然，後面的SQL語句使用的就是資料表或視圖了，如:

– table1是一個實際存在的表

with table1 as ( select * from persons where age < 30 )

select * from table1 – 使用了名為table1的公共表表達式

select * from table1 – 使用了名為table1的資料表

1. CTE 可以引用自身，也可以引用在同一WITH 子句中預先定義的CTE。不允許前向引用

6.不能在CTE_query_definition 中使用以下子句：

1）ORDER BY（除非指定了TOP 子句）2）COMPUTE 或COMPUTE BY 3）INTO 4）帶有查詢提示的OPTION 子句 5）FOR XML 6）FOR BROWSE

7.如果将CTE 用在屬于批處理的一部分的語句中，那麼在它之前的語句必須以分号結尾,如：

declare @s nvarchar(3)

set @s = ‘C%’

; – 必須加分号

with t_tree as ( select CountryRegionCode from person.CountryRegion where Name like @s)

select * from person.StateProvince where CountryRegionCode in (select * from t_tree)

與UNION ALL比較:

1.原sql ( union all ):

SELECT
  0                          REFUNDSUCCESSNUMBER,
  0                          REFUNDFAILNUMBER,
  COUNT(ID)                  REFUNDNUMBER,
  SUM(NVL(REFUND_AMOUNT, 0)) REFUNDAMOUNT,
  SUM(NVL(POINT, 0))         REFUNDPOINTS,
  CHANNEL,
  GATEWAY_CODE               GATEWAY
FROM PP_PAY
WHERE TRADE_TYPE = 1
      AND CREATE_TIME >= TO_DATE('20180819', 'YYYYMMDD') AND
      CREATE_TIME < TO_DATE('20180820', 'YYYYMMDD')
GROUP BY CHANNEL, GATEWAY_CODE
UNION ALL SELECT
            COUNT(ID)    REFUNDSUCCESSNUMBER,
            0            REFUNDFAILNUMBER,
            0            REFUNDNUMBER,
            0            REFUNDAMOUNT,
            0            REFUNDPOINTS,
            CHANNEL,
            GATEWAY_CODE GATEWAY
          FROM PP_PAY
          WHERE TRADE_TYPE = 1
                AND REFUND_STATUS = 1 AND
                CREATE_TIME >= TO_DATE('20180819', 'YYYYMMDD') AND CREATE_TIME < TO_DATE('20180820', 'YYYYMMDD')
          GROUP BY CHANNEL, GATEWAY_CODE
UNION ALL SELECT
            0            REFUNDSUCCESSNUMBER,
            COUNT(ID)    REFUNDFAILNUMBER,
            0            REFUNDNUMBER,
            0            REFUNDAMOUNT,
            0            REFUNDPOINTS,
            CHANNEL,
            GATEWAY_CODE GATEWAY
          FROM PP_PAY
          WHERE TRADE_TYPE = 1
                AND REFUND_STATUS >= 1 AND REFUND_STATUS <= 1 AND
                CREATE_TIME >= TO_DATE('20180819', 'YYYYMMDD') AND CREATE_TIME < TO_DATE('20180820', 'YYYYMMDD')
          GROUP BY CHANNEL, GATEWAY_CODE
           

2.用with as 替換後:

with refund as (select
                  0                     REFUNDSUCCESSNUMBER,
                  0                     REFUNDFAILNUMBER,
                  1                     REFUNDNUMBER,
                  NVL(REFUND_AMOUNT, 0) REFUNDAMOUNT,
                  NVL(POINT, 0)         POINT,
                  CHANNEL,
                  GATEWAY_CODE,
                  REFUND_STATUS
                from ULEAPP_PAYMENT.PP_PAY
                WHERE TRADE_TYPE = 1
                      AND CREATE_TIME >= TO_DATE('20180819', 'YYYYMMDD') AND
                      CREATE_TIME < TO_DATE('20180820', 'YYYYMMDD'))
 
 
SELECT
  0                         REFUNDSUCCESSNUMBER,
  0                         REFUNDFAILNUMBER,
  COUNT(REFUNDNUMBER)       REFUNDNUMBER,
  SUM(NVL(REFUNDAMOUNT, 0)) REFUNDAMOUNT,
  SUM(NVL(POINT, 0))        REFUNDPOINTS,
  CHANNEL,
  GATEWAY_CODE
FROM refund
GROUP BY CHANNEL, GATEWAY_CODE
UNION ALL SELECT
            COUNT(REFUNDSUCCESSNUMBER) REFUNDSUCCESSNUMBER,
            0                          REFUNDFAILNUMBER,
            0                          REFUNDNUMBER,
            0                          REFUNDAMOUNT,
            0                          REFUNDPOINTS,
            CHANNEL,
            GATEWAY_CODE
          FROM refund
          WHERE REFUND_STATUS = 1
          GROUP BY CHANNEL, GATEWAY_CODE
UNION ALL SELECT
            0                       REFUNDSUCCESSNUMBER,
            COUNT(REFUNDFAILNUMBER) REFUNDFAILNUMBER,
            0                       REFUNDNUMBER,
            0                       REFUNDAMOUNT,
            0                       REFUNDPOINTS,
            CHANNEL,
            GATEWAY_CODE
          FROM refund
          WHERE REFUND_STATUS >= 1 and REFUND_STATUS <= 1
          GROUP BY CHANNEL, GATEWAY_CODE
           

該表開發庫資料量10w條，CREATE_TIME索引生效，替換後查詢速度提提升在1倍以上。