I want to statistics over the past 12 months in the year to the total number of data of each month and the number of monthly new

This is the view of SQL (ACTS as a temporary table 12 months)
The CREATE ALGORITHM=UNDEFINED DEFINER=` root ` @ % ` ` SQL SECURITY DEFINER VIEW ` past_12_month_view ` AS SELECT
Date_format (curdate (), 'm % Y - %) AS ` month ` UNION
SELECT
Date_format ((curdate () - 1 MONTH INTERVAL), 'm % Y - %) AS ` MONTH ` UNION
SELECT
Date_format (curdate () - 2 MONTH INTERVAL), 'm % Y - %) AS ` MONTH ` UNION
SELECT
Date_format ((curdate () - 3 MONTH INTERVAL), 'm % Y - %) AS ` MONTH ` UNION
SELECT
Date_format ((curdate () - 4 MONTH INTERVAL), 'm % Y - %) AS ` MONTH ` UNION
SELECT
Date_format ((curdate () - 5 MONTH INTERVAL), 'm % Y - %) AS ` MONTH ` UNION
SELECT
Date_format ((curdate () - 6 MONTH INTERVAL), 'm % Y - %) AS ` MONTH ` UNION
SELECT
Date_format ((curdate () - 7 MONTH INTERVAL), 'm % Y - %) AS ` MONTH ` UNION
SELECT
Date_format ((curdate () - 8 MONTH INTERVAL), 'm % Y - %) AS ` MONTH ` UNION
SELECT
Date_format ((curdate () - 9 MONTH INTERVAL), 'm % Y - %) AS ` MONTH ` UNION
SELECT
Date_format ((curdate () - 10 MONTH INTERVAL), 'm % Y - %) AS ` MONTH ` UNION
SELECT
Date_format (curdate () - 11 MONTH INTERVAL), 'm % Y - %) AS ` MONTH `
This is to test the business table of table SQL
The CREATE TABLE ` user_released_info ` (
` id ` int (11), NOT NULL AUTO_INCREMENT,
` create_time ` timestamp NULL DEFAULT NULL,
DEFAULT NULL ` is_delete ` int (1) the COMMENT 'normal 1 0 delete'
The PRIMARY KEY (` id `)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
This is my SQL statement
SELECT
V.m onth,
Ifnull (t.a llCount, 0) AS allCount,
Ifnull (t.n owMonthCount, 0) AS nowMonthCount
The FROM
Past_12_month_view v
LEFT the JOIN (
SELECT
DATE_FORMAT (createTime, '% Y - % m) month,
NowMonthCount AS nowMonthCount,
@ allCount:=@ allCount + nowMonthCount AS allCount
The FROM
(
SELECT
The date (create_time) AS createTime,
The count (id) AS nowMonthCount
The FROM
User_released_info
WHERE
Is_delete=0
AND DATE_FORMAT (create_time, '% Y - % m) & gt; DATE_FORMAT (date_sub (curdate (), and 12 MONTH INTERVAL), '% Y - % m')
GROUP BY
DATE_FORMAT (create_time, '% Y - % m')
) AS temp,
(SELECT @ allCount:=0) AS t
) t ON v.m onth=t.m onth
GROUP BY
V.m onth

She has a problem, it is countless according to the new words as that month that month accumulative total is 0, such as the screenshots above I new a month in 2021-02 data into 13, I don't know how to change the above SQL seek help from god

CodePudding user response:

Now countless February, according to a new by the end of the 2021-02 allCount should be 12 (multiply) nowMonthCount should be 0

CodePudding user response:

SQL is too long, but play off is variable,
5.8 words directly using analysis function

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

CodePudding user response:

Written to fill a 5.7

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