(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

select

Union

00:00:00

from

5 条回复 • 2020-12-14 13:45:59 +08:00

mm163

2020-11-16 19:18:01 +08:00

union

GroupF

2020-11-17 14:26:13 +08:00

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

GroupF

2020-11-17 14:29:25 +08:00

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

GroupF

2020-11-17 14:38:40 +08:00

仅针对这个例子，我用 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)

GroupF

2020-12-14 13:45:59 +08:00

pps，占用大家公共资源了，用相同语句生成的临时表，就不可以 join

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

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

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

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

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