mysql统计近n天每天的数据量

第一步

查询近n天的日期（以查询最近七天为例）

SELECT
	@s :=@s + 1 AS _index,
	DATE(
		DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)
	) AS _date
FROM
	information_schema. TABLES,
	(SELECT @s := - 1) temp
WHERE
	@s < 6
ORDER BY
	_date

第二步

以第一步查询的查询左表left join我们要统计的表

SELECT
	tbl._date AS date,
	IFNULL(tbr.taskCount, 0) AS taskCount
FROM
	(
		SELECT
			@s :=@s + 1 AS _index,
			DATE(
				DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)
			) AS _date
		FROM
			information_schema. TABLES,
			(SELECT @s := - 1) temp
		WHERE
			@s <= 6
		ORDER BY
			_date
	) tbl
LEFT JOIN (
	SELECT
		count(*) AS taskCount,
		DATE(begin_time) startdate
	FROM
		t
	GROUP BY
		startdate
) AS tbr ON tbl._date = tbr.startdate
GROUP BY
	tbl._date

这里用到了两个函数：
DATE(expr):将时间转换成日期
IFNULL(expr1,expr2): 如果expr1为null，则值为expr2