MySql 统计最近12个月数据

直接上代码

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `db_lego_user`@`%` 
    SQL SECURITY DEFINER
VIEW `year_month_view` AS
    SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `year_month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `year_month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `year_month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `year_month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `year_month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `year_month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `year_month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `year_month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `year_month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `year_month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `year_month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `year_month`

select ym.year_month from year_month_view ym group by ym.year_month;

SELECT
	ymv.year_month yearMonth,
	ifnull(pi.totalPay, 0) totalPay
FROM
	year_month_view ymv
LEFT JOIN (
	SELECT
		DATE_FORMAT(pi.create_time, '%Y-%m') yearMonth,
		SUM(pi.actual_price) totalPay
	FROM
		t_order pi
	WHERE
		DATE_FORMAT(pi.create_time, '%Y-%m') > DATE_FORMAT(
			date_sub(curdate(), INTERVAL 12 MONTH),
			'%Y-%m'
		)
	GROUP BY
		yearMonth
) pi ON ymv.year_month = pi.yearMonth
GROUP BY
	ymv.year_month

本文地址：https://blog.csdn.net/weixin_44138647/article/details/110704585