mysql统计天、周、月、季度、半年、年
- 之前在网上搜索按时间统计,发现不是很全 ,接着别人的思路进行延伸下,
- mysql统计天、周、月、季度、半年、年
- 前期工作创建辅助表
CREATE TABLE num (i INT); INSERT INTO num (i) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
- 创建要查询的表
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(32) NOT NULL COMMENT '用户名称', `sex` char(1) DEFAULT NULL COMMENT '性别', `address` varchar(256) DEFAULT NULL COMMENT '地址', `create_time` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES ('1', '小南', '男', '湖南湘潭', '2019-07-01'); INSERT INTO `user` VALUES ('2', '小谭', '男', '湖南湘潭', '2019-07-02'); INSERT INTO `user` VALUES ('3', '小梅', '女', '广东梅县', '2019-07-01'); INSERT INTO `user` VALUES ('4', '小仪', '女', '四川仪陇', '2019-05-10'); INSERT INTO `user` VALUES ('5', '小蓝', '女', '上海嘉定', '2019-11-11'); INSERT INTO `user` VALUES ('6', '小苍', '男', '湖南湘潭', '2018-06-01'); INSERT INTO `user` VALUES ('7', '小萨', '男', '湖南湘潭', '2018-01-02'); INSERT INTO `user` VALUES ('8', '小静', '女', '广东梅县', '2018-06-01'); INSERT INTO `user` VALUES ('9', '大幂幂', '女', '四川仪陇', '2019-03-10'); INSERT INTO `user` VALUES ('10', '宋小宝', '女', '上海嘉定', '2019-05-11');
-
按天统计
SELECT temp.date, COALESCE (u.unmber, 0) 'number' FROM ( SELECT adddate('2019-07-01', numlist.id) AS 'date' FROM ( SELECT n1.i + n10.i * 10 + n100.i * 100 AS id FROM num n1 CROSS JOIN num AS n10 CROSS JOIN num AS n100 ) AS numlist WHERE adddate('2019-07-01', numlist.id) < date_add('2019-07-05',INTERVAL 1 DAY) ) temp LEFT JOIN ( SELECT LEFT (create_time, 10) AS udate, count(create_time) unmber FROM user WHERE 1=1 GROUP BY udate ) u ON temp.date = u.udate ORDER BY temp.date;
-
按周统计
SELECT temp.monthWeek, COALESCE (plan.number, 0) 'number' FROM ( SELECT YEARWEEK( adddate('2019-07-01', INTERVAL numlist.id WEEK),1 ) AS 'date', CONCAT( MONTH (date_format('2019-07-01', '%Y-%m-%d')),'月第', numlist.id + 1,'周') AS 'monthWeek' FROM ( SELECT * FROM ( SELECT n1.i + n10.i * 10 AS id FROM num n1 CROSS JOIN num AS n10 ) a WHERE a.id <= 11 ) AS numlist WHERE adddate('2019-07-01',INTERVAL numlist.id WEEK ) <= '2019-07-31' ) temp LEFT JOIN ( SELECT YEARWEEK( date_format(create_time, '%Y-%m-%d'), 1) date, count( YEARWEEK(date_format(create_time, '%Y-%m-%d'),1 ) ) number FROM USER WHERE 1 = 1 AND create_time >= '2019-07-01' AND create_time <= '2019-07-31' GROUP BY YEARWEEK( date_format(create_time, '%Y-%m-%d'), 1 ) ) plan ON temp.date = plan.date;
-
按月统计
SELECT LEFT (temp.date, 7) monthStr, COALESCE (u.unmber, 0) 'number' FROM ( SELECT adddate('2019-01-01',INTERVAL numlist.id MONTH ) AS 'date' FROM ( SELECT * FROM ( SELECT n1.i + n10.i * 10 AS id FROM num n1 CROSS JOIN num AS n10 ) a WHERE a.id <= 11 ) AS numlist WHERE adddate('2019-01-01',INTERVAL numlist.id MONTH) <= '2019-12-3' ) temp LEFT JOIN ( SELECT LEFT (create_time, 7) AS udate, count(create_time) unmber FROM user WHERE 1=1 GROUP BY udate ) u ON LEFT (temp.date, 7) = u.udate ORDER BY temp.date
-
按季度统计
SELECT temp.monthWeek, COALESCE(plan.number, 0) 'number' FROM ( SELECT QUARTER(adddate('2019-01-01', INTERVAL numlist.id QUARTER )) AS 'date', CONCAT(year(date_format('2019-01-01','%Y-%m-%d')), '年第', numlist.id +1, '季度') AS 'monthWeek' FROM ( SELECT * FROM ( SELECT n1.i + n10.i * 10 AS id FROM num n1 CROSS JOIN num AS n10 ) a WHERE a.id <= 11 ) AS numlist WHERE adddate('2019-01-01',INTERVAL numlist.id QUARTER) <= '2019-12-31' ) temp LEFT JOIN ( SELECT QUARTER(date_format(create_time,'%Y-%m-%d')) date, count(QUARTER(date_format(create_time,'%Y-%m-%d'))) number FROM user where 1=1 and create_time >= '2019-01-01' and create_time <= '2019-12-31' group by QUARTER(date_format(create_time,'%Y-%m-%d')) ) plan on temp.date = plan.date
-
按半年统计
SELECT bannian 'content', COALESCE (u.unmber, 0) 'number' FROM ( SELECT adddate( '2018-01-01',INTERVAL numlist.id MONTH) AS 'date', case DATE_FORMAT(adddate( '2018-01-01',INTERVAL numlist.id MONTH),'%c') when 7 then '下半年' when 8 then '下半年' when 9 then '下半年' when 10 then '下半年' when 11 then '下半年' when 12 then '下半年' else '上半年' end as bannian FROM ( SELECT * FROM( SELECT n1.i + n10.i * 10 AS id FROM num n1 CROSS JOIN num AS n10 ) a WHERE a.id <= 11 ) AS numlist WHERE adddate('2018-01-01',INTERVAL numlist.id MONTH) <= '2018-12-31' GROUP BY bannian ) temp LEFT JOIN ( SELECT case DATE_FORMAT(create_time,'%c') when 7 then '下半年' when 8 then '下半年' when 9 then '下半年' when 10 then '下半年' when 11 then '下半年' when 12 then '下半年' else '上半年' end as udate, count(create_time) unmber FROM user WHERE 1=1 and date_format(create_time,'%Y-%m-%d') >='2018-01-01' and date_format(create_time,'%Y-%m-%d') <= '2018-12-31' GROUP BY udate ) u ON bannian = u.udate ORDER BY bannian asc;
-
按年统计(近五年)
SELECT COALESCE(plan.number, 0) 'number', temp.date fiveYear FROM ( SELECT Year(adddate('2015-01-01', INTERVAL numlist.id Year )) AS 'date' FROM ( SELECT * FROM ( SELECT n1.i + n10.i * 10 AS id FROM num n1 CROSS JOIN num AS n10 ) a WHERE a.id <= 11 ) AS numlist WHERE adddate('2015-01-01',INTERVAL numlist.id Year) <= '2019-12-31' ) temp LEFT JOIN ( SELECT Year(date_format(create_time,'%Y-%m-%d')) date, count(Year(date_format(create_time,'%Y-%m-%d'))) number FROM user where 1=1 group by year(date_format(create_time,'%Y-%m-%d')) ) plan on temp.date = plan.date order by temp.date asc
-
技术交流群,海量学习资料免费获取,备注来意:就说博客上看到的,
Q群:289683917