MySQL数据库按日期汇总

/*按一年的周数汇总*/
/*超时*/

SELECT DATE_FORMAT(t.create_time, '%Y.%u') AS time,
(SELECT COUNT(id) from t_task where DATE_FORMAT(create_time, '%Y.%u') = DATE_FORMAT(t.create_time, '%Y.%u') and plan_sheet_id=1 and overtime = 2) as count
FROM t_task t GROUP BY time ORDER BY time DESC;

注：'%Y.%u' 其中u的大小写得到的时间不一样，U大写会比小写小1，小写应该是周数从0还是，U大写应该是从1开始
/*故障*/
SELECT DATE_FORMAT(t.create_time, '%Y.%u') AS time,
(SELECT COUNT(id) from t_task where DATE_FORMAT(create_time, '%Y.%u') = DATE_FORMAT(t.create_time, '%Y.%u') and plan_sheet_id=1 and (exception_count>0 or fault_count > 0)) as count
FROM t_task t GROUP BY time ORDER BY time DESC;

/*按一年的月数汇总*/
/*超时*/
SELECT DATE_FORMAT(t.create_time,'%Y%m') as months,
(SELECT COUNT(id) from t_task where DATE_FORMAT(create_time, '%Y.%m') = DATE_FORMAT(t.create_time, '%Y.%m')
and plan_sheet_id=1 and overtime = 2 ) as count
FROM t_task t GROUP BY months ORDER BY months DESC;
/*故障*/
SELECT DATE_FORMAT(t.create_time,'%Y%m') as months,
(SELECT COUNT(id) from t_task where DATE_FORMAT(create_time, '%Y.%m') = DATE_FORMAT(t.create_time, '%Y.%m')
and plan_sheet_id=1 and (exception_count>0 or fault_count > 0)) as count
FROM t_task t GROUP BY months ORDER BY months DESC;

/*按一年的季度数汇总*/
/*超时*/
SELECT quarter(create_time) as quart,
(SELECT COUNT(id) from t_task where quarter(create_time) = quarter(t.create_time)
and plan_sheet_id=1 and overtime = 2 ) as count
FROM t_task t GROUP BY quartORDER BY quartDESC;
/*故障*/
SELECT quarter(create_time) as quart,
(SELECT COUNT(id) from t_task where quarter(create_time) = quarter(t.create_time)
and plan_sheet_id=1 and (exception_count>0 or fault_count > 0) ) as count
FROM t_task t GROUP BY quart ORDER BY quartDESC;

 

注：本文是自己在网上找的一些资料整合一起的，结合自己需求改了一些，https://blog.csdn.net/kenhins/article/details/52814333，