mysql查询本月之前12个月的收入并分组

最新推荐文章于 2023-12-26 15:48:49 发布

沐码人.

最新推荐文章于 2023-12-26 15:48:49 发布

阅读量602

点赞数

分类专栏：项目相关文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_42232182/article/details/125555820

版权

项目相关专栏收录该内容

2 篇文章 0 订阅

订阅专栏

SELECT 
	DATE_FORMAT(date,'%Y-%m'), 
	IFNULL( sum( r.price ), 0 ) totalIncome
FROM (
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH AS date UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 2 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 3 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 4 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 5 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 6 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 7 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 8 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 9 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 10 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 11 MONTH UNION ALL
    SELECT LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 12 MONTH
) AS dates
LEFT JOIN xxx r ON r.created_at >= date AND r.created_at < date + INTERVAL 1 MONTH
GROUP BY date
ORDER BY date desc