MySQL complex query uses temporary table / with as (similar to table variable)

Query requirement ：
If the first one SQL Can you find out the result , Returns the result , otherwise , Implement Article 2 SQL, Return to Article 2 SQL Result .

SQL Server How to use table variables in :
If you find out “wangwang” The user returns , Otherwise, query “zhangzhang” User id return . Priority level

declare @temp table(id int)
insert into @temp
select id
from user
where name = 'wangwang'
if not exists(select id from @temp)
begin
  select id
  from user
  where name='zhangzhang'
end else
begin
  select id from @temp
end

MySQL There are no table variables in the , Only temporary tables can be used instead of ：

CREATE TEMPORARY TABLE temp1(id int ); --  Create a temporary table 1
insert into temp1 
select id
from user  
where name='wangwang'; 

CREATE TEMPORARY TABLE temp2(id int ); --  Create a temporary table 2
insert into temp2 
select id
from user 
where name='zhangzhang'; 

CREATE TEMPORARY TABLE temp1_re select id from temp1; --  Copy temporary table 1

select (case when (SELECT count(*) FROM temp1_re)>0 then --  Use case when Judge 
 (select id from temp1 order by id desc limit 1) else (
select id from temp2 order by id desc limit 1) end ) id ; 

Be careful ：
（1） Need to copy temporary table 1, Because “ In the same query In the sentence , You can only look up the temporary table once ”, Otherwise, the report will be wrong “can’t reopen ”. When there is not much data in the table , You can copy tables for operations .
（2） Because in mybatis Of xml More than one SQL, Database connection needs to be set allowMultiQueries=true.

2019-10-29 to update ：
Find out MySQL There is a similar SQLserver Statements of table variables in , That's it with as, Also called subquery part （subquery factoring）, You can define a SQL fragment , The SQL The fragment will be replaced by the whole SQL Statement use , for example ：

with temp_a as(
select id,name from user where name='wangwang'
),
temp_b as(
select id,name from user where name='zhangzhang'
)
select (case when (SELECT count(*) FROM temp_a)>0 then --  Use case when Judge 
 			(select id from temp_a order by id desc limit 1) 
 		else 
			(select id from temp_b order by id desc limit 1) 
		end 
	) id 

This way of writing is MySQL 8.0 Characteristics of
Reference resources ：https://www.cnblogs.com/Niko12230/p/5945133.html
https://stackoverflow.com/questions/324935/mysql-with-clause