这条统计sql该怎么改

[cwmlow]

我想统计过去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 求大神帮助

[cwmlow]

比如现在2月无数据新增， 截止到2021-02 allCount应该是12（累加） nowMonthCount应该是0

[nayi_224]

sql太长不看，不过估计就是变量玩脱了。 5.8的话直接用分析函数

select t1.*,
	   sum(ifnull(t2.num, 0)) over(order by t1.month) nnn
  from past_12_month_view t1 left join
  (
select '2020-04' month, 1 num union all
select '2020-06' month, 1 num union all
select '2020-07' month, 1 num 
) t2 on t1.month = t2.month
 order by t1.month

[nayi_224]

补一个5.7的写法

select t1.month,
	   @temp:=@temp+ifnull(t1.num, 0) nnn
  from (
select t1.*,
	   t2.num
  from past_12_month_view t1 left join
  (
select '2020-04' month, 1 num union all
select '2020-06' month, 1 num union all
select '2020-07' month, 1 num 
) t2 on t1.month = t2.month left join
  (select @temp:=0) t3 on 1 = 1
 order by t1.month) t1