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

- 日理万妓 2021-11-11 08:50 1585阅读 2赞

**一、有些统计页面需要达到如下效果（按照月份分组计算数据）**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70]  
二、在这里需要使用到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

举例如下：  
![在这里插入图片描述][2019080514571346.png]

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

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

![在这里插入图片描述][20190805150417993.png]

三、先看看执行结果，然后在详解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

结果如下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70 1]

四、分步解释sql

前提：需要一张中间表num，数据如下  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70 3]  
然后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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70 4]

参考:https://blog.csdn.net/JavaReact/article/details/79994555

仍在不断学习中，欢迎大家留言

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70]: /images/20211109/ce217ed406474748b82e91149feffbea.png
[2019080514571346.png]: /images/20211109/5e5c2a3165b44657b20aa344e8865764.png
[20190805150417993.png]: /images/20211109/fdfab2e00be449d090f9ca116c1135bd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70 1]: /images/20211109/fe162edc546c4c7dafb43472c85da8c3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70 2]: /images/20211109/93a93581adc44c79aad9641bb5d9382c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70 3]: /images/20211109/744d04416d2b417abe8a23032eeaa104.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3NDkzNTU2_size_16_color_FFFFFF_t_70 4]: /images/20211109/f73b7f19b18245ed8252457201a2976f.png