mysql查询今年的12个⽉的统计数据-沉睡个人博客

mysql查询今年的12个⽉的统计数据

1,046人已阅读时间：2022-05-14 09:20:54

### mysql查询今年的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

3、根据⽉份查询

select
    concat(month(years.date),'⽉') as year,
    years.date,
    date_format(years.date, '%Y-%m') AS years,
    sum(CASE WHEN delete_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 t_asilver c on MONTH(c.create_time) =MONTH(years.date) 
  	GROUP BY years.date order by years

上一篇：查询近一个月的数据

下一篇：【java】QueryWrapper方法解释