mysql-按天/月统计数据,没有的填充为0

最新推荐文章于 2024-10-18 17:49:30 发布

AP0906424

最新推荐文章于 2024-10-18 17:49:30 发布

阅读量1.6k

点赞数

文章标签： mysql 数据库

原文链接：https://blog.csdn.net/JavaReact/article/details/79994555

版权

文章介绍了如何利用MySQL的DATE_ADD(),DATE_SUB()和DAYOFYEAR()等函数进行日期操作，特别是在统计按月份分组的数据时的作用。通过示例SQL查询，展示了如何结合中间表和LEFTJOIN来实现特定的数据聚合和统计分析。

摘要由CSDN通过智能技术生成

一、有些统计页面需要达到如下效果（按照月份分组计算数据）
在这里插入图片描述
二、在这里需要使用到mysql的几个函数


1、DATE_ADD() 函数向日期添加指定的时间间隔。

语法：
DATE_ADD(date,INTERVAL expr unit)

date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。unit是指定的时间间隔类型单位。

unit参数可以是下列值：
unit值

MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

举例如下：
在这里插入图片描述

2、DATE_SUB() 函数从日期减去指定的时间间隔。
语法：
DATE_SUB(date,INTERVAL expr unit)
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。unit是指定的时间间隔类型单位。

所用的unit单位和上面的DATE_ADD()完全一致


3、DAYOFYEAR(date)
date 参数是合法的日期表达式
返回date在一年中的日数, 在1到366范围内。

在这里插入图片描述

三、先看看执行结果，然后在详解sql

SELECT
	LEFT (temp.date, 7) AS dateAbscissa,
	IFNULL(u.unmber, 0) AS thisYearCost
FROM
	(
		SELECT
			date_add(
				DATE_SUB(
					CURDATE(),
					INTERVAL dayofyear(now()) - 1 DAY
				),
				INTERVAL numlist.id MONTH
			) AS 'date'
		FROM
			(
				SELECT * FROM (SELECT i AS id FROM num) a
				WHERE a.id <= 11
			) AS numlist
		WHERE
			adddate(
				DATE_SUB(
					CURDATE(),
					INTERVAL dayofyear(now()) - 1 DAY
				),
				INTERVAL numlist.id MONTH
			) <= concat(YEAR(now()), '-12-31')
	) temp
LEFT JOIN (
	SELECT
		LEFT (c.statistics_date, 7) AS udate,
		SUM(c.operation_number) AS unmber
	FROM
		t_hospitalization_operation c
	WHERE
		1 = 1
	GROUP BY
		LEFT (c.statistics_date, 7)
) u ON LEFT (temp.date, 7) = u.udate
ORDER BY
	temp.date

结果如下
在这里插入图片描述

四、分步解释sql

前提：需要一张中间表num，数据如下
在这里插入图片描述

然后LEFT JOIN 本身的数据表，既可达到效果,

# 按照天数统计
 SELECT
		temp.date as dateAbscissa,
		IFNULL(u.singleDiseasesNum,0) as singleDiseasesNum,
		IFNULL(u.multidisciplinaryNum,0) as multidisciplinaryNum
  from(   SELECT DATE_ADD((date_add(date_add(last_day(now()),interval 1 day),interval -1 month)),INTERVAL  numlist.id  DAY)AS 'date' FROM
		(
			SELECT * from
				(SELECT i AS id FROM num ) a
			where a.id <=30 
		) AS numlist
      
) temp
	LEFT JOIN
(
	SELECT 
		DATE(c.create_time) AS udate,
		COUNT(if( c.audit_opinion='3' ,1,NULL) )as singleDiseasesNum,  
		COUNT(if( c.audit_opinion='4' ,1,NULL) )as multidisciplinaryNum 
	FROM
		t_mdt_apply_list c
	WHERE
	1 = 1
	GROUP BY DATE(c.create_time)
) u on DATE(temp.date) = u.udate ORDER BY temp.date

在这里插入图片描述