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