mysql with 查询_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、实例

6-1.png

在上述的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;

结果示意图如下：

6-2.png