使用mysql统计表中本周每一天的数量

Published on with 0 views and 0 comments

查询本周每一天对应的数据

SELECT
	c.DAY AS DAY,
	max( c.num ) AS num 
FROM
	(
	SELECT
		dayofweek( a.create_time )- 1 AS DAY,
	IF
		(
			count( 1 ) IS NULL,
			0,
		count( 1 )) AS num 
	FROM
		sys_upload_file a 
	WHERE
		date_format( a.create_time, '%Y-%m-%d' ) >= subdate( curdate(), date_format( curdate(), '%w' )- 1 ) 
		AND date_format( a.create_time, '%Y-%m-%d' ) <= subdate( curdate(), date_format( curdate(), '%w' )- 7 ) 
	GROUP BY
		dayofweek( a.create_time )- 1 UNION
	SELECT
		* 
	FROM
		(
		SELECT
			0 DAY,
			0 num UNION
		SELECT
			1,
			0 UNION
		SELECT
			2,
			0 UNION
		SELECT
			3,
			0 UNION
		SELECT
			4,
			0 UNION
		SELECT
			5,
			0 UNION
		SELECT
			6,
			0 
		) b 
	) c 
GROUP BY
	c.DAY

查询结果

  image.png

↑ ↓