含义:WITH AS 短语,也叫做子查询部分(subquery factoring)也称公用表表达式(CTE),
-
,可以定义一个SQL片断,该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高,也可以在UNION ALL的不同部分,作为提供数据的部分。
对于UNION ALL,使用WITH AS定义了一个UNION ALL语句,当该片断被调用2次以上,优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示meterialize则是强制将WITH AS短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。
-
本质:其实有点类似于一个容器,将查询的数据保存在容器表中,供全局使用,性能更好,写法如下:
cr 就相当于一个表
with
cr as
(
select CountryRegionCode from person.CountryRegion where Name like 'C%'
)
select * from person.StateProvince where CountryRegionCode in (select * from cr) 注意点:
-
CTE 后面必须直接跟使用CTE 的SQL 语句(如select、insert、update等),否则,CTE 将失效。如下面的SQL语句将无法正常使用CTE
with
cr as
(
select CountryRegionCode from person.CountryRegion where Name like 'C%'
)
select * from person.CountryRegion -- 应将这条SQL语句去掉
-- 使用CTE的SQL语句应紧跟在相关的CTE后面 --
select * from person.StateProvince where CountryRegionCode in (select * from cr) -
如果CTE 的表达式名称与某个数据表或视图重名,则紧跟在该CTE 后面的SQL 语句使用的仍然是CTE,当然,后面的SQL 语句使用的就是数据表或视图了,如下面的SQL 语句所示:
-- table1是一个实际存在的表
with
table1 as
(
select * from persons where age < 30
)
select * from table1 -- 使用了名为table1的公共表表达式
select * from table1 -- 使用了名为table1的数据表 -
CTE后面也可以跟其他的CTE,但只能使用一个with,多个CTE中间用逗号(,)分隔,如下面的SQL语句所示
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
8.注意有的时候会出一些很奇葩的问题,如果写的都对但依然报错,可以在 With 前面加; 有可能会好(只要是批处理的操作,都要在With 前面加;)
; WITH tableOne AS