With As多表查询

目录

语法

举例

表关系

 分析

mysql进程状态

         使用左连接

---

语法

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进程状态

  使用左连接

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的内容来看，猜测图一应该是执行了两次查询处理，图二可能只有一次。