cwmlow 码农  02月22日
Mysql请教这条统计sql该怎么改
我想统计过去12个月 截止到每个月的数据总数和每月新增的数量

这是视图sql (充当12个月的临时表)
CREATE ALGORITHM = UNDEFINED DEFINER = `root` @`%` SQL SECURITY DEFINER VIEW `past_12_month_view` AS SELECT
date_format( curdate( ), '%Y-%m' ) AS `month` UNION
SELECT
date_format( ( curdate( ) - INTERVAL 1 MONTH ), '%Y-%m' ) AS `month` UNION
SELECT
date_format( ( curdate( ) - INTERVAL 2 MONTH ), '%Y-%m' ) AS `month` UNION
SELECT
date_format( ( curdate( ) - INTERVAL 3 MONTH ), '%Y-%m' ) AS `month` UNION
SELECT
date_format( ( curdate( ) - INTERVAL 4 MONTH ), '%Y-%m' ) AS `month` UNION
SELECT
date_format( ( curdate( ) - INTERVAL 5 MONTH ), '%Y-%m' ) AS `month` UNION
SELECT
date_format( ( curdate( ) - INTERVAL 6 MONTH ), '%Y-%m' ) AS `month` UNION
SELECT
date_format( ( curdate( ) - INTERVAL 7 MONTH ), '%Y-%m' ) AS `month` UNION
SELECT
date_format( ( curdate( ) - INTERVAL 8 MONTH ), '%Y-%m' ) AS `month` UNION
SELECT
date_format( ( curdate( ) - INTERVAL 9 MONTH ), '%Y-%m' ) AS `month` UNION
SELECT
date_format( ( curdate( ) - INTERVAL 10 MONTH ), '%Y-%m' ) AS `month` UNION
SELECT
date_format( ( curdate( ) - INTERVAL 11 MONTH ), '%Y-%m' ) AS `month`
这是测试业务表的建表sql
CREATE TABLE `user_released_info` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`create_time` timestamp NULL DEFAULT NULL,
`is_delete` int(1) DEFAULT NULL COMMENT ' 0 正常 1删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
这是我的sql语句
SELECT
v.month,
ifnull( t.allCount, 0 ) AS allCount,
ifnull( t.nowMonthCount, 0 ) AS nowMonthCount
FROM
past_12_month_view v
LEFT JOIN (
SELECT
DATE_FORMAT( createTime, '%Y-%m' ) month,
nowMonthCount AS nowMonthCount,
@allCount := @allCount + nowMonthCount AS allCount
FROM
(
SELECT
date( create_time ) AS createTime,
count( id ) AS nowMonthCount
FROM
user_released_info
WHERE
is_delete = 0
AND DATE_FORMAT( create_time, '%Y-%m' ) > DATE_FORMAT( date_sub( curdate( ), INTERVAL 12 MONTH ), '%Y-%m' )
GROUP BY
DATE_FORMAT( create_time, '%Y-%m' )
) AS temp,
( SELECT @allCount := 0 ) AS t
) t ON v.month = t.month
GROUP BY
v.month

现在有个问题,就是当月无数据新增的话 截止当月总数累加会是0,比如上面的截图 我新增一条2021-02月份的数据 才会变成13条,我不知道该怎么改上面这个sql 求大神帮助
...全文
浏览 点赞 收藏 1
写回复
1 条回复

还没有回复,快来抢沙发~

发动态
发帖子
MySQL
创建于2007-09-28

2.4w+

社区成员

5.5w+

社区内容

MySQL相关内容讨论专区
社区公告
暂无公告