在mysql中,“with as”也叫子查询,用于定义一个sql片段,且该片段会被整个sql语句反复使用很多次,这个sql片段就相当于是一个公用临时表,语法为“with tmp as (查询语句)”。
with tmp as (select * from tb_name), tmp2 as (select * from tb_name2), tmp3 as (select * from tb_name3), …
–相当于建了个e临时表
with e as (select * from scott.emp e where e.empno=7499) select * from e;
–相当于建了e、d临时表
with e as (select * from scott.emp), d as (select * from scott.dept) select * from e, d where e.deptno = d.deptno;
其实就是把一大堆重复用到的sql语句放在with as里面,取一个别名,后面的查询就可以用它,这样对于大批量的sql语句起到一个优化的作用,而且清楚明了。
推荐学习:mysql视频教程