mysql连接共享临时表_mysql 5.7 两个临时表 左右连接，同样的临时表 join 连接不上...

直接能跑的 sql 奉上 ，可能命名不太规范

左表为主表就出左表，右表为主表出右表

select * from

(select

DATE_FORMAT(m1, '%Y-%m') as day3

from

(

select

('2020-01-23 00:00:00' - INTERVAL DAYOFMONTH('2020-01-23 00:00:00')-1 DAY)

+INTERVAL m MONTH as m1

from

(

select @rownum:=@rownum+1 as m from

(select 1 union select 2 union select 3 union select 4) t1,

(select 1 union select 2 union select 3 union select 4) t2,

(select 1 union select 2 union select 3 union select 4) t3,

(select @rownum:=-1) t0

) d1

) d2

where m1<='2020-12-01 00:00:00'

order by m1) as datemonths3

left join (select

DATE_FORMAT(m1, '%Y-%m') as day1

from

(

select

('2020-01-23 00:00:00' - INTERVAL DAYOFMONTH('2020-01-23 00:00:00')-1 DAY)

+INTERVAL m MONTH as m1

from

(

select @rownum:=@rownum+1 as m from

(select 1 union select 2 union select 3 union select 4) t1,

(select 1 union select 2 union select 3 union select 4) t2,

(select 1 union select 2 union select 3 union select 4) t3,

(select @rownum:=-1) t0

) d1

) d2

where m1<='2020-12-01 00:00:00'

order by m1) as datemonths2 on datemonths2.day1 = datemonths3.day3