我想統計過去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 求大神幫助
uj5u.com熱心網友回復：
比如現在2月無資料新增， 截止到2021-02 allCount應該是12（累加） nowMonthCount應該是0轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/262242.html
標籤：MySQL
上一篇：Oracle 11g使用IMP匯入時總是提示IMP-00019: 由于 ORACLE 錯誤 1 而拒絕行 IMP-00003: 遇到 ORACLE 錯誤 1 O