[mysql] 월별통계 처리할때 참고한 블로그 (데이터 없는 날짜 0 처리)

블로그 url : https://chobopark.tistory.com/167

 

create table t (n int);
insert into t values (1);
 
insert into t select * from t; -- 13번 실행
 
create table temp_date (temp_date char(10)); 
insert into temp_date
select temp_date from (
  select @rnum:=@rnum+1 as rownum, date(adddate('2021-01-01', interval @rnum day)) as temp_date
  from (select @rnum:=-1) r, t
  ) t
where year(temp_date) < 2031; 
 
select 
    *
from (
    select
        date_format(aa.temp_date, '%Y-%m') date,
        count(c.name) as cnt
    from
        temp_date aa
            left join 
                tb_user c on (concat(substring(c.reg_date,1,4),'-',substring(c.reg_date,5,2)) = substring(aa.temp_date,1,7))
            group by 
                date
) a
where 
    date <= concat (DATE_FORMAT(now(),'%Y'),'-', DATE_FORMAT(now(),'%M'))
order by a.date desc
limit 12