Mysql请教这条统计sql该怎么改

cwmlow 2021-02-22 11:53:39

我想统计过去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