MySQL database Summary by date, mysql database Summary

MySQL database Summary by date, mysql database Summary

/* Summary by the number of weeks in a year */
/* Timeout */

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;

Note: '% Y. in % U', the u is case-insensitive and the u is 1 lower than the lower case, the lower case should be the week number from 0 or, and the u should start from 1
/* Fault */
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 (prediction_count> 0 or fault_count> 0) as count
FROM t_task t group by time order by time DESC;

/* Summary by the number of months per year */
/* Timeout */
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;
/* Fault */
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 (prediction_count> 0 or fault_count> 0) as count
FROM t_task t group by months order by months DESC;

/* Quarterly summary by year */
/* Timeout */
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;
/* Fault */
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 (prediction_count> 0 or fault_count> 0) as count
FROM t_task t group by quart order by quartDESC;

 

Note: This article is on the Internet to find some information integrated, combined with their own needs to change some, https://blog.csdn.net/kenhins/article/details/52814333,