联系QQ:884563046点击这里给我发消息

按年度、季度、月度、周、日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

微信(不常用)和QQ同号: 884563046点击这里给我发消息

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注