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) 有效

2. 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的数据表

5. 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倍以上。