sql查询今年的12个月的统计数据

1.查出1~12

SELECT @xi:=@xi+1 as id from 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3) xc1, 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,  
			(SELECT @xi:=0) xc0  

2.查出当年的1~12月

/**查询当年的第一天与最后一天*/

SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY);

SELECT concat(YEAR(now()),'-12-31');


/**查询当年12个月*/
select adddate(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), INTERVAL numlist.id-1 month) as 'date', numlist.id 
    from (SELECT @xi:=@xi+1 as id from 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3) xc1, 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,  
			(SELECT @xi:=0) xc0  ) as numlist 

/**查询过去12个月*/
select DATE_FORMAT((CURDATE() - INTERVAL numlist.id MONTH), '%Y-%m') as 'date', numlist.id 
    from (SELECT @xi:=@xi+1 as id from 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3) xc1, 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,  
			(SELECT @xi:=0) xc0  ) as numlist 

3.根据月份查询

select
    concat(month(years.date),'月') as year,
    years.date,
    date_format(years.date, '%Y-%m') AS years,
    sum(CASE WHEN del_flag = 0 THEN 1 ELSE 0 END) AS sum
from
    (  select adddate(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), INTERVAL numlist.id-1 month) as 'date', numlist.id 
    from (SELECT @xi:=@xi+1 as id from 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3) xc1, 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) xc2,  
			(SELECT @xi:=0) xc0  ) as numlist 
    where adddate(DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY), INTERVAL numlist.id month) <= concat(YEAR(now())+1,'-1-1')) years
		left join yc_contract c on MONTH(c.create_time) =MONTH(years.date) 
		GROUP BY years.date order by years 

4.结果