【SQL学习笔记6】with as的用法

一、with as 短语含义

WITH AS短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高，也可以在UNION ALL的不同部分，作为提供数据的部分。减少嵌套查询可以提升sql的查询效率

二、with as 用法

例如之前的join实例：

select stu_name,grade from
student
join
(select stu_id,grade from course 
join 
grade
on grade.stu_id=course.stu_id
where couname='语文') as a
on a.stu_id=student.stu_id

也可以通过with as短语实现，如下：

with t1 
as select stu_id
,grade 
from cource 
join 
grade 
on grade.stu_id=course.stu_id
where couname='语文'
select stu_name
,grade
from t1 
join 
student
on 
student.stu_id=t1._stuid

也可以使用多个as，创建多个子查询部分，如：

with t1 
as select id from table_a
,t2 as select id from table_b
,t3 as select id from table_c
selet
t1.id from 
t1
join 
t2
on t1.id=t2.id
join
t3
on t2.id=t3.id