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;
上述两个查询结果是一样的，执行时间也是基本相似，都是0.183s，也就是说基本对性能相差无几。
但是从上述id的内容来看，猜测图一应该是执行了两次查询处理，图二可能只有一次。
