直接上代码
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