mysql 按日期删除数据库_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，