MySQL查询年月周的求和数据

神秘的周先生

于 2020-05-23 17:46:34 发布

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

文章标签： mysql sql

本文链接：https://blog.csdn.net/qq_32245639/article/details/106303577

本文介绍了如何使用MySQL查询特定年份的每月和每周的求和数据，涉及时间字段fcreate_time和求和字段fline_num。通过YEARWEEK()函数获取周数，结合%Y-%m-%u格式展示年-周数。同时提供链接分享了更详细的查询方法。

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

求和查询某一年的每个月数据

fcreate_time --> 时间字段
fline_num --> 求和字段

SELECT
	sum( CASE MONTH ( a.fcreate_time ) WHEN '1' THEN a.fline_num ELSE 0 END ) AS january,
	sum( CASE MONTH ( a.fcreate_time ) WHEN '2' THEN a.fline_num ELSE 0 END ) AS february,
	sum( CASE MONTH ( a.fcreate_time ) WHEN '3' THEN a.fline_num ELSE 0 END ) AS march,
	sum( CASE MONTH ( a.fcreate_time ) WHEN '4' THEN a.fline_num ELSE 0 END ) AS april,
	sum( CASE MONTH ( a.fcreate_time ) WHEN '5' THEN a.fline_num ELSE 0 END ) AS may,
	sum( CASE MONTH ( a.fcreate_time ) WHEN '6' THEN a.fline_num ELSE 0 END ) AS june,
	sum( CASE MONTH ( a.fcreate_time ) WHEN '7' THEN a.fline_num ELSE 0 END ) AS july,
	sum( CASE MONTH ( a.fcreate_time ) WHEN '8' THEN a.fline_num ELSE 0 END ) AS august,
	sum( CASE MONTH ( a.fcreate_time ) WHEN '9' THEN a.fline_num ELSE 0 END ) AS september,
	sum( CASE MONTH ( a.fcreate_time ) WHEN '10' THEN a