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

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

1,105人已阅读时间：2022-07-17 11:57:51

## 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

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.结果

![image.png](http://blog.liuzhuo.xin:443/profile/upload/2022/07/17/4c1778dc-ede9-4507-8018-15ca1cbb9fcf.png)

上一篇：【java】Java判断⼀个时间是否在时间区间内

下一篇：【java】Spring Boot获取GET请求参数