WITH AS

WITH AS 用法详解

WITH AS 短语，也叫做子查询部分（subquery factoring），可以定义一个 SQL 片断，该 SQL 片断会被整个 SQL 语句用到。可以使 SQL 语句的可读性更高，也可以在 UNION ALL 的不同部分，作为提供数据的部分。比如下面：

;with c as (
	select  '张三' as name
)
select * from cCode language: JavaScript (javascript)

使用 CTE 时的注意事项

1. 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)Code language: JavaScript (javascript)

1. 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.idCode language: JavaScript (javascript)

1. 如果 CTE 的表达式名称与某个数据表或视图重名，则紧跟在该 CTE 后面的 SQL 语句使用的仍然是 CTE；当然，后面的 SQL 语句使用的就是数据表或视图了，如下面的 SQL 语句所示：

-- table1是一个实际存在的表

with
table1 as
(
    select * from persons where age < 30
)
select * from table1  -- 使用了名为table1的公共表表达式
select * from table1  -- 使用了名为table1的数据表Code language: JavaScript (javascript)

多 CTE 嵌套与 UNION ALL 示例

with stu as
(
	SELECT [id]
		  ,[name]
		  ,[age]
		  ,[birthday]
		  ,[nation]
		  ,[classname]
		  ,[sex]
	  FROM [School].[dbo].[Student]
	  where age = 18
  ) ,
  stu2 as
(
	SELECT [id]
		  ,[name]
		  ,[age]
		  ,[birthday]
		  ,[nation]
		  ,[classname]
		  ,[sex]
	  FROM stu
	  where name = '李四'
  ) 
  select * from stu2 
  union all
  select * from stuCode language: JavaScript (javascript)

CTE 与实体表同名示例

with Money as
(
	SELECT [id]
		  ,[name]
		  ,[age]
		  ,[birthday]
		  ,[nation]
		  ,[classname]
		  ,[sex]
	  FROM [School].[dbo].[Student]
	  where age = 18
  ) 
  select * from Money -- 搜索的是CTE片段
  select * from Money -- 搜索的是真正的Money表Code language: JavaScript (javascript)