MySQL 种with as 与 create table as 联用

with as临时表通常()结束后select, 那么怎么跟create table as 联用呢，建表使用with as 语句

--1 use one temp table for staging the data,
drop table if exists tmp.tmp_table;
create table tmp.tmp_table as
	with a as(select id,XXX from table1 where id <=12)
	,lz as(select id,xxx FROM table1 where id >=40
			union all
			select id,xxx from table1 where id>=16 and id<20
			)
select a.*,lz.* 
		,CURRENT_TIMESTAMP as create_time,CURRENT_TIMESTAMP as update_time,'ur-name' as creator
from a,lz
order by lz.id;


--or can use nested query
drop table if exists tmp.tmp_table;
create table tmp.tmp_table as
with a as(select id,XXX from table1 where id <=12)
select lz.* from(
    select max(id) from a --change the query code in this place
)

--2 insert all correct data into target table, do watch out both table has same column name in order and number if you are using select *, normally we are recommended to select specific column name for better maintenance experiences.

INSERT INTO dbname.fact_table
select * from tmp.tmp_table;

直接insert into 用with as 报语法错，可能是某些版本不支持，所以还是用个临时表转换一下，再把临时表数据全insert into 进去好了。