MySQL 查询指定年份12个月份的订单支付金额

Marttin2

于 2021-12-08 15:34:17 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： sql 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_39822451/article/details/121792706

sql 专栏收录该内容

101 篇文章

订阅专栏

该查询展示了2021年每个用户的订单支付金额分布，按月份进行汇总，包括1月到12月的详细数据，便于分析用户年度消费行为。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


-- MySQL 查询指定年份12个月份的订单支付金额
SELECT
		user_id,
		SUM(case month(a.create_time) when 1 then money else 0 end) AS Jan,
		SUM(case month(a.create_time) when 2 then money else 0 end) AS Feb,
		SUM(case month(a.create_time) when 3 then money else 0 end) AS Mar,
		SUM(case month(a.create_time) when 4 then money else 0 end) AS Apr,
		SUM(case month(a.create_time) when 5 then money else 0 end) AS May,
		SUM(case month(a.create_time) when 6 then money else 0 end) AS Jun,
		SUM(case month(a.create_time) when 7 then money else 0 end) AS Jul,
		SUM(case month(a.create_time) when 8 then money else 0 end) AS Aug,
		SUM(case month(a.create_time) when 9 then money else 0 end) AS Sep,
		SUM(case month(a.create_time) when 10 then money else 0 end) AS Oct,
		SUM(case month(a.create_time) when 11 then money else 0 end) AS Nov,
		SUM(case month(a.create_time) when 12 then money else 0 end) AS `Dec`
FROM
			sys_order a
WHERE
			year(a.create_time) = 2021
GROUP BY
			user_id