mysql 统计本周本年_mysql统计本年-优快云博客

这篇博客展示了如何使用SQL查询来统计本周、本月以及本年的日期，并将其转换为星期和月份的中文表示。查询涉及到日期的加减运算、格式化以及条件判断，用于数据统计和分析。

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

统计本周


SELECT
	@cdate := date_add( @cdate, INTERVAL + 1 DAY ) date,
CASE
		date_format( @cdate, '%w' ) 
		WHEN '1' THEN
		'星期一' 
		WHEN '2' THEN
		'星期二' 
		WHEN '3' THEN
		'星期三' 
		WHEN '4' THEN
		'星期四' 
		WHEN '5' THEN
		'星期五' 
		WHEN '6' THEN
		'星期六' 
		WHEN '0' THEN
		'星期日' 
	END AS 'week' 
FROM
	(
	SELECT
		@cdate := date_add( DATE_FORMAT( SUBDATE( CURDATE(), DATE_FORMAT( CURDATE(), '%w' )- 1 ), '%Y-%m-%d' ), INTERVAL - 1 DAY ) 
	FROM
		dev_telemetry_history_site_1 
		LIMIT 7 
	) a

WHEN '4' THEN
    '四月'
WHEN '5' THEN
    '五月'
WHEN '6' THEN
    '六月'
WHEN '7' THEN
    '七月'
WHEN '8' THEN
    '八月'
WHEN '9' THEN
    '九月'
WHEN '10' THEN
    '十月'
WHEN '11' THEN
    '十一月'
WHEN '12' THEN
    '十二月'
END AS 'week'
FROM ( SELECT @cdate := date_add(concat(YEAR(now()),'-01-01'), INTERVAL -1 MONTH ) FROM dev_telemetry_history_site_1 LIMIT 12  ) a

统计本年

SELECT
	DATE_FORMAT( @cdate := date_add( @cdate, INTERVAL + 1 MONTH ), '%Y-%m' ) date,
CASE
		date_format( @cdate, '%c' ) 
		WHEN '1' THEN
		'一月' 
		WHEN '2' THEN
		'二月' 
		WHEN '3' THEN
		'三月' 
		WHEN '4' THEN
		'四月' 
		WHEN '5' THEN
		'五月' 
		WHEN '6' THEN
		'六月' 
		WHEN '7' THEN
		'七月' 
		WHEN '8' THEN
		'八月' 
		WHEN '9' THEN
		'九月' 
		WHEN '10' THEN
		'十月' 
		WHEN '11' THEN
		'十一月' 
		WHEN '12' THEN
		'十二月' 
	END AS 'week' 
FROM
	( SELECT @cdate := date_add( concat( YEAR ( now()), '-01-01' ), INTERVAL - 1 MONTH ) FROM dev_telemetry_history_site_1 LIMIT 12 ) a

本月


SELECT
	ts.date,
	IFNULL( d.kwh, 0 ) kwh 
FROM
	(
	SELECT
		d.date 
	FROM
		(
		SELECT
			@cdate := date_add( @cdate, INTERVAL + 1 DAY ) date 
		FROM
			(
			SELECT
				@cdate := date_add( date_add( curdate(), INTERVAL - DAY ( curdate()) + 1 DAY ), INTERVAL - 1 DAY ) 
			FROM
				dev_telemetry_history_site_1 
				LIMIT 31 
			) a 
		) d 
	WHERE
	#用来控制本月的日期
	   d.date <= last_day(
		curdate()) 
	) ts
	LEFT JOIN ( SELECT SUM( kwh ) kwh, ts FROM daily_kwh_statistics WHERE id IN ( 3, 2 ) GROUP BY dev_id, ts ) d ON ts.date = d.ts