MySQL查询 年月周的求和数据

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

1.     fcreate_time --> 时间字段
2.      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.fline_num ELSE 0 END ) AS october,
	sum( CASE MONTH ( a.fcreate_time ) WHEN '11' THEN a.fline_num ELSE 0 END ) AS november,
	sum( CASE MONTH ( a.fcreate_time ) WHEN '12' THEN a.fline_num ELSE 0 END ) AS december 
FROM
	cf_statistics_chart AS a 
WHERE
	1 = 1 
	AND DATE_FORMAT( a.fcreate_time, '%Y' ) = 2020 
	AND a.fchart_type = 1;

查询结果：

查询一个月每周的求和数据

列举几个其他的可以去看这个地址， 大佬已经整理好了

https://blog.csdn.net/youcai35/article/details/40655451

1. %a 缩写星期名
2. %u 周 (00-53) 星期一是一周的第一天
3. %Y 年，4 位 （2020） %y 年，2 位（20）
4. %m 月，数值(00-12)

SELECT
	DATE_FORMAT( fcreate_time, '%Y-%m-%u' ) AS fcreateTime,
	SUM(a.fline_num) AS flineNum 
FROM
	cf_statistics_chart as  a
WHERE
	1 = 1 
	AND DATE_FORMAT( a.fcreate_time, '%Y-%m' ) = '2020-05'
	AND a.fchart_type = '1'
	GROUP BY fcreateTime
	ORDER BY fcreateTime

查询结果：

2020-25-19 // 注： -19不是19号， 是这一年的第19周， 这是格式（%Y-%m-%u）

查询当前周的求和数据：

当前周 YEARWEEK=（ now() ）

上一周 YEARWEEK=( now() ) -1

WHERE YEARWEEK( DATE_FORMAT( csc.fcreate_time, '%Y-%m-%d' ) ) = YEARWEEK( now( ) )

SELECT
	DATE_FORMAT( csc.fcreate_time, '%Y-%m-%d' ) AS fcreateTime1,
	date_format(csc.fcreate_time,'%a') as fcreateTime,
	SUM(csc.fline_num) as flineNum 
FROM
	cf_statistics_chart AS csc 
WHERE
	YEARWEEK( DATE_FORMAT( csc.fcreate_time, '%Y-%m-%d' ) ) = YEARWEEK( now( ) )
	GROUP BY fcreateTime
	ORDER BY fcreateTime1

查询结果：

小弟能力有限， 大概就这些了。有什么不对的欢迎指正，随时修改  Thanks♪(･ω･)ﾉ

最后

以上就是洁净日记本为你收集整理的MySQL查询 年月周的求和数据的全部内容，希望文章能够帮你解决MySQL查询 年月周的求和数据所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。