mysql 按年度、季度、月度、周、日SQL统计查询

2019独角兽企业重金招聘Python工程师标准>>>

 表结构：

 表中数据总条数：2926

1. 统计每天的单数

  SELECT COUNT(*),DATE_FORMAT(t.create_time,'%Y-%m-%d') from t_call_record t GROUP BY DATE_FORMAT(t.create_time,'%Y-%m-%d')  （执行时间：0.003s）

   SELECT count(*),DATE_FORMAT(t.create_time,'%Y-%m-%d') from t_call_record t GROUP BY TO_DAYS(t.create_time)  (执行时间：0.002s)

第二种优于第一种，会列出所有的单子

1.2 统计上个月的天数据

SELECT count(*),DATE_FORMAT(t.create_time,'%Y-%m-%d') from t_call_record t where YEAR(t.create_time) = '2018' GROUP BY DAY(t.create_time)
（执行时间：0.001s）按天统计从上个月最后一天向前推的31天数据

1.3 按周统计数据

SELECT count(*),WEEK(t.create_time),MONTH(t.create_time) from t_call_record t where YEAR(t.create_time) = '2018' GROUP BY WEEK(t.create_time)（执行时间：0.002s）
1.4 按月统计数据

SELECT count(*),MONTH(t.create_time) from t_call_record t where YEAR(t.create_time) = '2018' GROUP BY MONTH(t.create_time)
（执行时间：0.002s）

1.5 按照年统计数据

SELECT count(*),YEAR(t.create_time) from t_call_record t where YEAR(t.create_time) = '2018' GROUP BY YEAR(t.create_time)(执行时间：0.001s)
1.6 按照季度统计数据

SELECT count(*),QUARTER(t.create_time) from t_call_record t where YEAR(t.create_time) = '2018' GROUP BY QUARTER(t.create_time)
（执行时间：0.001s）

1.7 查询本年度数据

SELECT * from t_call_record t WHERE year(t.create_time) = year(curdate()) （执行时间：0.005s）

1.8 查询本季度数据

SELECT *  from t_call_record where quater(t.create_time) = QUARTER(curdate()) （执行时间0.004s）

1.9 查询本月度数据

SELECT * from t_call_record  where MONTH(create_time) = MONTH(CURDATE()) and year(create_time) = year(curdate())（执行时间：0.004s）

1.20 查询本周数据

SELECT * from t_call_record t where MONTH(t.create_time) = MONTH(CURDATE()) and WEEK(t.create_time) = WEEK(CURDATE())（执行时间0.003s）

1.21 查询近5天的记录

SELECT * from t_call_record t where TO_DAYS(NOW())-TO_DAYS(t.create_time) <= 5（执行时间： 0.003s）

2. mysql获取当天,昨天,本周,本月,上周,上月的起始时间
https://blog.youkuaiyun.com/qq_34775102/article/details/82776168

3. 有关现实统计的问题总结

  我们会遇到统计数据的时候，有时候放假，在这段时间没有进行工作，这段时间是没有数据，我们统计的时候想显示数据，

 这样就在数据库中维护一张t_date 表：表结构如下：

DROP TABLE IF EXISTS `t_date`;
CREATE TABLE `t_date` (
  `date` date NOT NULL COMMENT '日期',
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
数据截图：

 可以用它做关联表查询，让他分组，然后left join 查询表，实际查询的数据，on  为 week(t.create_time) = week(date)：

某个业务列子：

  测试表，结构如下，可以往里面弄点测试数据

DROP TABLE IF EXISTS `t_order`;
CREATE TABLE `t_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `weight` double DEFAULT NULL,
  `amount` double(255,0) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

查询语句：

SELECT d.date,IFNULL(ROUND(A.total_weight,2),0), IFNULL(ROUND(A.total_amount,2),0) 
from (SELECT MIN(date) date  from t_date 
GROUP BY week(date) ) d
LEFT JOIN (
SELECT MIN(DATE_FORMAT(t.create_time,'%Y-%m-%d')) create_time,SUM(t.weight) total_weight ,
SUM(t.amount) total_amount 
from t_order t 
GROUP BY week(t.create_time) ) A
on WEEK(d.date) = WEEK(A.create_time)
ORDER BY d.date

转载于:https://my.oschina.net/kuchawyz/blog/2252228