我想統計過去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應該是0uj5u.com熱心網友回復：
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
uj5u.com熱心網友回復：
補一個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
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/262714.html
標籤：Web 開發
上一篇：kindEditor編輯器批量上傳圖片，連圖片都看不到
下一篇：結束下載行程或禁止下載