mysql数据库查询按月统计数量语句_按年度、季度、月度、周、日SQL统计查询数据库语句...

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.csdn.net/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