MySQL 进行按连续月/日统计查询，没有的月份为0

最新推荐文章于 2024-04-26 18:40:57 发布

呆。@473

最新推荐文章于 2024-04-26 18:40:57 发布

阅读量858

点赞数

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_60382322/article/details/130280365

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

MySQL 进行按连续月/日统计查询

按连续天查询，天数条件 6 > num，创建临时时间（/天）表为基础表去进行关联表查询。

WITH recursive convertYear ( dt, num ) AS (
            SELECT
                date_format( now(), '%Y-%m-%d' ),
                @num := 1 UNION ALL
        SELECT
            date_format( date_sub( now(), INTERVAL num DAY ), '%Y-%m-%d' ),
            @num := @num + 1
        FROM
            convertYear
        WHERE
           6 > num
            )
	 SELECT a.dt AS time FROM convertYear a

执行结果
在这里插入图片描述

按天统计示例：

WITH recursive convertYear ( dt, num ) AS (
            SELECT
                date_format( now(), '%Y-%m-%d' ),
                @num := 1 UNION ALL
        SELECT
            date_format( date_sub( now(), INTERVAL num DAY ), '%Y-%m-%d' ),
            @num := @num + 1
        FROM
            convertYear
        WHERE
           6 > num
            )
        SELECT a.dt AS time,
	IFNULL(b.num,0) as learning,
	IFNULL(c.num,0) as completed
        FROM convertYear a
            LEFT JOIN (SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as createTime,COUNT(1) as num from study WHERE `status` = '3' GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d')) b ON a.dt = b.createTime
            LEFT JOIN (SELECT DATE_FORMAT(update_time,'%Y-%m-%d') as updateTime,COUNT(1) as num from study WHERE `status` = '4'  GROUP BY DATE_FORMAT(update_time,'%Y-%m-%d')) c ON a.dt = c.updateTime

使用convertYear临时表当基础表去关联查询即可。
结果：统计出每天的数据。
在这里插入图片描述

按月统计示例：

WITH recursive convertYear ( dt, num ) AS (
            SELECT
                date_format( now(), '%Y-%m' ),
                @num := 1 UNION ALL
        SELECT
            date_format( date_sub( now(), INTERVAL num MONTH ), '%Y-%m' ),
            @num := @num + 1
        FROM
            convertYear
        WHERE
           6 > num
            )
        SELECT a.dt AS time,
	IFNULL(b.num,0) as learning,
	IFNULL(c.num,0) as completed
        FROM convertYear a
            LEFT JOIN (SELECT DATE_FORMAT(create_time,'%Y-%m') as createTime,COUNT(1) as num from study WHERE `status` = '3' GROUP BY DATE_FORMAT(create_time,'%Y-%m')) b ON a.dt = b.createTime
            LEFT JOIN (SELECT DATE_FORMAT(update_time,'%Y-%m') as updateTime,COUNT(1) as num from study WHERE `status` = '4'  GROUP BY DATE_FORMAT(update_time,'%Y-%m')) c ON a.dt = c.updateTime

结果:
在这里插入图片描述