数据库查询每月数据，没有的月份补0

感谢提出的方法https://juejin.cn/post/6995003591807221767

场景：

要根据数据查出每月的统计数据。

思路：

        1、创建一个临时表，里面存放月份        

select from_unixtime(unix_timestamp( date_trunc('month',date_trunc('year',now())) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 1 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 2 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 3 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 4 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 5 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 6 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 7 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 8 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 9 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 10 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(date_trunc('year',now()), interval 11 month)) ),'yyyyMM') as tm

注：这个算是比较复杂的一种写法，每个月是计算出来的。也可以直接写1、2、3月，但是做关联的时候就要多加一个今年的判断条件

简单写法：

SELECT *  from(   
 select 1 as mon
 union all
 select 2 as mon
 union all
 select 3 as mon
 union all
 select 4 as mon
 union all
 select 5 as mon
 union all
 select 6 as mon
 union all
 select 7 as mon
 union all
 select 8 as mon
 union all
 select 9 as mon
 union all
 select 10 as mon
 union all
 select 11 as mon
 union all
 select 12 as mon
 ) a
 left join
(select *
from c
where YEAR(tm)=YEAR (NOW())
GROUP BY mon) b on b.mon=a.mon

        2、然后将数据与临时表做乘积，将每一条数据都分成12份。 

select 
			*
		from 
		(
		select from_unixtime(unix_timestamp( date_trunc('month',now()) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 1 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 2 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 3 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 4 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 5 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 6 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 7 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 8 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 9 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 10 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 11 month)) ),'yyyyMM') as tm
		) a 
		left join cntrct b on 1=1 

        3、最后再将数据根据月份左关联起来

select 
			*
		from 
		(
		select from_unixtime(unix_timestamp( date_trunc('month',now()) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 1 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 2 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 3 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 4 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 5 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 6 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 7 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 8 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 9 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 10 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 11 month)) ),'yyyyMM') as tm
		) a 
		left join ods_pro.t_ods_cm_cntrct b on 1=1 
		left join (
			select cntrct_id,from_unixtime(unix_timestamp( oper_tm ),'yyyyMM') tm,sum(cast (mny as DOUBLE )) mny from cntrct_stts 
			where typ = '3' 
			group by cntrct_id,from_unixtime(unix_timestamp( oper_tm ),'yyyyMM')
		) c on b.id = c.cntrct_id and a.tm = c.tm

        4、将多列转换成一行

select 
			b.id,
			ifnull(max(case when a.tm like '%01' then c.mny end),0) Jan,
			ifnull(max(case when a.tm like '%02' then c.mny end),0) Feb,
			ifnull(max(case when a.tm like '%03' then c.mny end),0) Mar,
			ifnull(max(case when a.tm like '%04' then c.mny end),0) Apr,
			ifnull(max(case when a.tm like '%05' then c.mny end),0) May,
			ifnull(max(case when a.tm like '%06' then c.mny end),0) Jun,
			ifnull(max(case when a.tm like '%07' then c.mny end),0) Jul,
			ifnull(max(case when a.tm like '%08' then c.mny end),0) Aug,
			ifnull(max(case when a.tm like '%09' then c.mny end),0) Sep,
			ifnull(max(case when a.tm like '%10' then c.mny end),0) Oct,
			ifnull(max(case when a.tm like '%11' then c.mny end),0) Nov,
			ifnull(max(case when a.tm like '%12' then c.mny end),0) `Dec`
		from 
		(
		select from_unixtime(unix_timestamp( date_trunc('month',now()) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 1 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 2 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 3 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 4 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 5 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 6 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 7 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 8 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 9 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 10 month)) ),'yyyyMM') as tm
		union all
		select from_unixtime(unix_timestamp( date_trunc('month',date_add(NOW(), interval 11 month)) ),'yyyyMM') as tm
		) a 
		left join ods_pro.t_ods_cm_cntrct b on 1=1 
		left join (
			select cntrct_id,from_unixtime(unix_timestamp( oper_tm ),'yyyyMM') tm,sum(cast (mny as DOUBLE )) mny from cntrct_stts 
			where typ = '3' 
			group by cntrct_id,from_unixtime(unix_timestamp( oper_tm ),'yyyyMM')
		) c on b.id = c.cntrct_id and a.tm = c.tm
		group by b.id