SQL学习笔记----第一天（通过With子句检索学生选课名字、高校归属城市名）

前言

本节使用With子句来完成一些比较复杂的查询功能

---

一、With子句

with子句一般出现在复杂的查询中，有助于将更复杂的大型查询分解为更简单的查询，增加SQL代码的可读性，每一个with子句查询可以看作一个视图，数据暂存在内存中。
with子查询不但可以在select语句中使用，而且也支持在delete、update、insert语句中，并且with子句必须在使用之前定义，定义格式为：

with 别名 as （子查询）

定义好别名之后就可以在SQL中通过别名来引用子查询了。首先看下这个包含子查询的SQL：

select c.cid,c.cname from cource c where c.cid in (select e.cid from elective e where e.sid = '1001')

这条SQL的作用是查询编号为1001学生的选课课程信息，下面将sql改写成with子句的形式：

with  v_elective as (select e.cid from elective e where e.sid = '1001');
select c.cid,c.cname from course c inner join v_elective a on c.cid = a.cid

with子句查询获得的结果集和上面利用子查询的结果是一样的

也可以在with子查询中为返回的字段定义别名：

with v_elective(id) as (select e.cid from elective e where e.sid='1001');
select c.cid,c.cname from course c inner join v_elective a on c.cid=a.id

这条SQL是将子查询中返回的字段e.cid重新定义了别名为id，在主查询中就可以使用别名id了。下面一起看下怎么使用with子句查询高效的归属城市名称：

with v_school as (select* from school where name="暨南大学")
select b.name,a.name as city_name,a.provinceid from city a inner join v_school b on a.id=b.cityid

结果集：

下面一个例子更可以体现with子句的优势，首先使用内联结查询高校个数在50以上的城市名称：

select a.name as city_name,b.v_count from city a inner join(select cityid.count(1) as v_count from school group by cityid)b on a.id = b.cityid where v_count>50; 

在使用with子句实现同样的查询功能：

with v_school as (select cityid,count(1) as v_count from school group by cityid);
select a.name as city_name,b.v_count from city a inner join v_school b on a.id = b.cityid where v_count>50

结果集：

两种方式查询方式尽管返回结果集是一样的，但是with子句更加简洁，可读性更强。

二、With后面跟多个子句

with后面可以跟着多个子句，但只能使用一个with，多个子句中间用逗号：

with course_cte as (select cid,cname from course c where credit>3),
	 student_cte as (select sid,stu_name from student s where s.age>30);
select c.cname,s.stu_name,e.grade from elective e
	   inner join course_cte c on c.cid = e.cid
	   inner join student_cte s on s.sid = e.sid;

结果集：

上面的with后面紧跟两个子查询，别名分别为course_cte和student_ste，这样在主查询中直接引用这两个别名，看起来就会简洁很多。

总结

提示：这里对文章进行总结：
本次学习我们主要是使用了with子句使复杂的查询变的更加通俗易懂，with子句就像定义了一个临时表，可以在后面的查询语句中使用该临时表，避免了多张表堆积在一个SQL语句中的情况。