mysql 按时间段统计(年,季度,月,天,时)
按年汇总,统计:
select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y');
按月汇总,统计:
select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y-%m');
按季度汇总,统计:
select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));
select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));
按小时:
select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by date_format(col, '%Y-%m-%d %H ');
查询 本年度的数据:
SELECT * FROM mytable WHERE year(FROM_UNIXTIME(my_time)) = year(curdate())
查询数据附带季度数:
SELECT id, quarter(FROM_UNIXTIME(my_time)) FROM mytable;
查询 本季度的数据:
SELECT * FROM mytable WHERE quarter(FROM_UNIXTIME(my_time)) = quarter(curdate());
本月统计:
select * from mytable where month(my_time1) = month(curdate()) and year(my_time2) = year(curdate())
本周统计:
select * from mytable where month(my_time1) = month(curdate()) and week(my_time2) = week(curdate())
N天内记录:
WHERE TO_DAYS(NOW())-TO_DAYS(时间字段)<=N
本篇文章来源于 Linux公社网站(www.linuxidc.com) 原文链接:http://www.linuxidc.com/Linux/2012-11/74145.htm
最新文章
- c++减法高精度算法
- 如何在UMG中上显示3D物体
- 移动表格行 解决低版本IE fadeIn fadeOut 失效问题
- mysql备份工具 :mysqldump mydumper Xtrabackup 原理
- Linux内核,文件系统移植过程中出现的一些问题与解决办法
- struts接收参数方式
- android获得屏幕高度和宽度
- linux内核之网络协议栈
- Android----paint触摸轨迹监听
- 字典NSDictionary的常见用法
- archlinux系统安装博通B43XX系列无线网卡驱动
- 一起写框架-Ioc内核容器的实现-基础功能-ComponentScan(四)
- Django学习(2)数据宝库
- 20171201 - macOS High Sierra 神级 bug
- memset函数的实现&;printf函数几种输出格式的输出结果
- cmake安装配置及入门指南
- 关于eric4和pyqt的入门学习(转)
- Sallen-Key Active Butterworth Low Pass Filter Calculator
- 分布式存储Seaweedfs源码分析
- MTD 移动目标防御技术