未分類
25 11 月 2020

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

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

資深大佬 : GroupF 4

直接能跑的 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:[email protected]+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:[email protected]+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

大佬有話說 (4)

  • 資深大佬 : mm163

    union

  • 主 資深大佬 : GroupF

    @mm163 但是 union，完成不了 join 的事情，我固定条数，没有补 0

  • 主 資深大佬 : GroupF

    再加上条件应该能行，那我就是在表连接以及用函数的时候他们连接，成为新表时候的顺序是怎么
    举个小例子
    select * from
    ((select ‘2020’ as b,’20211′ as a ) union
    (select ‘2021’ as b,’20211′ ) ) as av
    group by av.a

  • 主 資深大佬 : GroupF

    仅针对这个例子，我用 having 还是能满足大概
    #分组，a 只能出现一次，但是可能 b 没数据，选有数据的分
    select * from
    ((select ‘2020’ as b,’20221′ as a ) union
    (select ‘2021’ as b,’20221′ ) ) as av
    group by av.a having (b > 0 or b=0)