With As多表查询
目录
语法
with
alias_name_01 as (查询SQL select * from table_name_01)
alias_name_02 as (查询SQL select * from alias_name_01 an01,table_name_02 tn02 where an01.foreign_id = tn02.id)
select * from alias_name_02 ;
举例
表关系
CREATE TABLE ims.`ims_app_dept_info` (
`app_dept_id` int NOT NULL AUTO_INCREMENT,
`p_app_dept_id` int DEFAULT NULL,
`dept_name` varchar(50) DEFAULT NULL COMMENT '部门名称',
PRIMARY KEY (`app_dept_id`)
) COMMENT='组织机构表';
CREATE TABLE ims.`ims_app_employee_info` (
`app_emp_id` int NOT NULL AUTO_INCREMENT,
`emp_name` varchar(50) DEFAULT NULL COMMENT '员工姓名',
`emp_code` varchar(50) DEFAULT NULL COMMENT '员工编码',
`app_dept_id` int DEFAULT NULL,
PRIMARY KEY (`app_emp_id`)
) COMMENT='员工信息表';
如上所示,有一个员工表,一个组织表,员工表关联到组织表,通过app_dept_id关联。
with
emp_info as (
select * from ims.ims_app_employee_info t limit 100
),
emp_all_info as (
select
ei.emp_name,ei.emp_code,dept.dept_name
from emp_info ei,
ims.ims_app_dept_info dept
where
dept.app_dept_id = ei.app_dept_id
)
select * from emp_all_info limit 3;
使用上面的SQL,就可以查询两个表之间的数据,而不需要使用left join 关联。
分析
用explain可以看到这个SQL执行的大概过程。
首先是从表t,也就是ims_app_employee_info中过滤出100条数据,这个过程是很快的。
然后就是从ims_app_dept_info表,根据主键关联,速度也是很快的。
最后是table=derived3这个表,这是一个假表,应该是临时表,没有用到任何key,所以应该是从100条数据中过滤出来3条。
再看这张执行进程状态过程图,这里面查询时间总共是0.333s,
从上图大概能猜到,创建了临时表用于存放这些查询数据,后面就是执行with这个SQL语句。
这里有三个query end状态,并不是说执行了三次,也不是说移除了两张临时表。
此处应该是进程执行是有时间的,query end表示查询结束,可以开始处理数据。
而查询的数据是放在临时表中,数据处理完毕后会处于 removing tmp table。
其实可以做个测试,比如explain select * from ims.ims_app_dept_info t,有时候也是会有三个query end状态。
mysql进程状态
query end | 这种状态发生后处理查询。 |
removing tmp table | 线程移除临时表。 如果没有创建临时表,这种状态是不使用。 |
closing tables | 线程改变表中的数据刷新到磁盘和关闭使用的表。 |
end | 这发生在最后的清理工作,有ALTER TABLE , CREATE VIEW , DELETE , INSERT , SELECT或UPDATE语句。 |
freeing items | 线程已执行的命令。 释放一些在这种状态下完成的项目,涉及的查询缓存。 这种状态通常后跟cleaning up 。 |
init | 在发生之前的初始化ALTER TABLE , DELETE , INSERT , SELECT或UPDATE语句。 服务器在这种状态下采取的行动包括冲洗二进制日志中InnoDB日志,和一些查询缓存清理操作 |
optimizing | 服务器执行查询的初始优化 |
preparing | 这种状态出现在查询优化过程中。 |
使用左连接
explain with
emp_info as (
select * from ims.ims_app_employee_info t limit 3
),
emp_all_info as (
select ei.emp_name,ei.emp_code,dept.dept_name
from emp_info ei
left join ims.ims_app_dept_info dept
on dept.app_dept_id = ei.app_dept_id
)
select * from emp_all_info;
explain
select e.emp_name,e.emp_code,t.dept_name
from ims.ims_app_employee_info e
left join ims.ims_app_dept_info t
on t.app_dept_id = e.app_dept_id
limit 3;
![图一](https://img-blog.csdnimg.cn/fd8b76dc880f4640ae63f837703ac737.png)
![](https://img-blog.csdnimg.cn/6b9f1fc5a6ee424a84faec840990c15f.png)
上述两个查询结果是一样的,执行时间也是基本相似,都是0.183s,也就是说基本对性能相差无几。
但是从上述id的内容来看,猜测图一应该是执行了两次查询处理,图二可能只有一次。