当前位置:网站首页>【mysql】mysql分别按年/月/日/周分组统计数据
【mysql】mysql分别按年/月/日/周分组统计数据
2022-08-10 23:50:00 【冰冷的希望】
1.统计
我们可以使用date_format()函数格式化时间,然后进行分组操作
例如有一个学生表,结构如下
id | name | age | height | gender | create_time |
---|---|---|---|---|---|
1 | pan | 27 | 169 | 1 | 2022-01-13 10:20:22 |
2 | yang | 18 | 177 | 1 | 2022-03-14 09:16:42 |
3 | daisy | 25 | 156 | 2 | 2022-07-19 19:58:03 |
-- 按年
select date_format(create_time, '%Y') years,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by years;
-- 按月
select date_format(create_time, '%Y-%m') months,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by months;
-- 按周
select date_format(create_time, '%Y-%u') weeks,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by weeks;
-- 按日
select date_format(create_time, '%Y-%m-%d') days,avg(age),count(gender) from student where create_time > "2022-01-01 00:00:00" and create_time < "2022-07-19 19:58:03" group by days;
如果不想用date_format函数,可以使用对应的year()/month()/week()/day()函数替代
2.占位符
date_format()需要传入一个特定的占位符,mysql常用的占位符可参考下表
占位符 | 说明 |
---|---|
%Y | 年(4位) |
%y | 年(2位) |
%M | 月(英文名,如January) |
%m | 月(数字,如01) |
%D | 日(英文名,如1st) |
%d | 日(数字,如01) |
%e | 日(数字,如1) |
%U | 一年中的第几周,从0开始 ,周日是第一天 |
%u | 一年中的第几周,从0开始,周一是第一天 |
%H | 时,24小时制,例如15 |
%h | 时,12小时制,例如01 |
%i | 分 |
%s | 秒 |
边栏推荐
- [Excel知识技能] 将数值格式数字转换为文本格式
- 9. Rest style request processing
- Ali P7 bask in January payroll: hard to fill the, really sweet...
- 【C语言篇】操作符之 位运算符详解(“ << ”,“ >> ”,“ & ”,“ | ”,“ ^ ”,“ ~ ”)
- nodejs项目连接mysql数据库
- 报错:Client does not support authentication protocol requested by server; consider upgrading MySQL cli
- Based on the SSM to reach the phone sales mall system
- 13. Content Negotiation
- 虚拟电厂可视化大屏,深挖痛点精准减碳
- How to determine how many bases a number is?
猜你喜欢
随机推荐
虚拟电厂可视化大屏,深挖痛点精准减碳
【C语言】初识指针
Qt入门(六)——抽奖系统的实现
I caught a 10-year-old Ali test developer, and after talking about it, I made a lot of money...
翻译软件哪个准确度高【免费】
基于Web的疫情隔离区订餐系统
【C语言】二分查找(折半查找)
13. Content Negotiation
Design and Realization of Employment Management System in Colleges and Universities
【C语言】C语言程序设计:动态通讯录(顺序表实现)
Based on the SSM to reach the phone sales mall system
给肯德基打工的调料商,年赚两亿
报错:Client does not support authentication protocol requested by server; consider upgrading MySQL cli
Part of the reserve bank is out of date
高校就业管理系统设计与实现
sqlmap结合dnslog快速注入
nodejs项目连接mysql数据库
Mysql. Slow Sql
线上突然查询变慢怎么核查
YOLOv5的Tricks | 【Trick13】YOLOv5的detect.py脚本的解析与简化