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，