欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL数据库按日期汇总

程序员文章站 2022-04-19 16:59:58
/*按一年的周数汇总*//*超时*/ SELECT DATE_FORMAT(t.create_time, '%Y.%u') AS time, (SELECT COUNT(id) from t_task where DATE_FORMAT(create_time, '%Y.%u') = DATE_FO ......

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

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,