语法:
1.结构: with 别名 as ( 公共sql片段 )
2.CTE(公共表达式)后面必须直接紧跟使用CTE的SQL语句,否则失效;
如:
- with cr as (select CountryRegionCode from person.CountryRegion where Name like ‘C%’ )
select * from person.StateProvince where CountryRegionCode in (select * from cr) 有效
- 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的数据表
- 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倍以上。