CTE：SQL with as 语法

CTE：SQL with as 语法

with as 定义

CTE 即 common table express，我们常用的 with as 短语，也叫做子查询部分（subquery factoring），主要是定义一个SQL片段，该SQL片段会被整个SQL语句所用到，也有可能在union all的不同部分，作为提供数据的部分。特别对于UNION ALL比较有用，因为union all的每一个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以使用with as短语，则只要执行一遍即可。如果with as短语所定义的表名被调用两次以上，则优化器会自动将with as 短语所获取的数据放入一个temp表里。

CTE（common table express）语法

WITH Common_table_express [(column_name[,n])] AS (CTE_query_definitation)

例如：

1
2
3

with events_cte as (select event_name,event_time,ip,ua from event_table where ds==20210101)

select * from events_cte

其中cte是公用表表达式，该表达式在使用上与表变量类似，只是SQL 在处理方式上不同。

使用CTE 注意事项

1)CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则CTE将失效

1
2
3
4
5

with events_cte as (select event_name,event_time,ip,ua from event_table where ds==20210101)

select * from user

select * from events_cte

with events_cte as 与最后面使用events_cte的语句之间不应该有其他语句，应该去掉select * from user后面使用的events_cte才有效。

2) CTE后面可以跟其他的CTE，但只能使用一个with，多个CTE之间用逗号（，）分隔，例如：

1
2
3
4

with cte1 as (select * from table1 where name like 'abc%'),
cte2 as (select * from table2 where id>2),
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

3) 如果CTE的名称与实际表名或者视图名称相同，那么紧随cte后面的针对cte名称的操作是针对CTE的，而再紧接的cte名称操作则是针对实际表名或视图的。

4) CTE可以引用自身，也可以引用在同一with子句中预先定义的cte。不允许向前引用。

5) 不允许在CTE_Query_Definition中使用以下子句：

1. COMPUTE 或COMPUTE BY(计算或分组计算)
2. ORDER BY(除非指定了TOP子句)
3. INTO
4. 带有查询提示的OPTION子句
5. FOR XML
6. FOR BROWSE

6) 如果是将CTE用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾，如下：

1
2
3
4
5

declare @dt long set @dt=20210101
;
with events_cte as (select event_name,event_time,ip,ua from event_table where ds==@dt)

select * from events_cte

7)with cte as()不能嵌套使用

转摘自：https://www.cnblogs.com/xmliu/p/7085644.html