mysql笔记(6)-多表查询之with

本文将简单介绍with子句的格式和用法

1、目的

通过创建一张（或多张）临时表，实现数据的抽离，从而方便进行数据的比较

2、格式

//创建2张临时表，并在这2张临时表的基础上进行查询
with 
temporary_table1_name(column_name,column_name) as 
(select xxx
from xxx),
temporary_table2_name(column_name,column_name) as 
(select xxx
from xxx)
select xxx
from temporary_table1_name,temporary_table2_name;

在上述语句中
temporary_table_name及其后面的column_name 都可以由自己指定

注意：创建的临时表仅可以用于此次查询，并不会写入数据库的物理内存中

3、实例

在上述的instructor表中，实现以下查询：
找出 院系总工资 高于 所有院系的平均总工资的 院系名称

思路如下：
（1）创建临时表1，使用group by和SUM函数计算各个院系的总工资;
（2）创建临时表2，对临时表1的所有行 求平均值，从而得到所有院系的平均总工资；
（3）用常规的select+where语句在2张临时表的基础上进行查询

with 
dept_total(dept_name,value) as
(select dept_name, SUM(salary)
from instructor
group by dept_name),
dept_total_avg(value) as
(select AVG(salary)
from dept_total)
select dept_name
from dept_total, dept_total_avg
where dept_total.value>=dept_total_avg.value;

结果示意图如下：