mysql 统计本周 本年

统计本周

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