WITH AS 用法详解

WITH AS 用法详解

WITH AS 类似一个 SQL 片段，类似一个临时表的作用。

with nv as
(
	SELECT *
	FROM [dbo].[学生信息] s
	where s.性别 = '女'
	union all
	SELECT *
	FROM [dbo].[学生信息] s
	where s.性别 = '男'
)
select * from nv
join dbo.班级信息 as c
on nv.所属班级 = c.班级编号Code language: JavaScript (javascript)

还可以使用逗号来进行多个 WITH AS 的使用，第二个 WITH AS 不需要再用 WITH 关键字，直接写别名即可：

with nv as
(
	SELECT *
	FROM [dbo].[学生信息] s
	where s.性别 = '女'
	union all
	SELECT *
	FROM [dbo].[学生信息] s
	where s.性别 = '男'
)
,
nv2 as
(
	select * from nv
	join dbo.班级信息 as c
	on nv.所属班级 = c.班级编号
)
,
nv3 as
(
	select * from nv2
	where nv2.班级人数 = 20
)
select * from nv3Code language: JavaScript (javascript)