MySQL统计函数记录
可使用常见的算术操作符。注意就 -、 +和 *而言, 若两个参数均为正数,则其计算结果的精确度为 BIGINT (64比特),若其中一个参数
按年汇总,统计:
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(时间字段)
,上一篇: Redis数据持久化
推荐阅读
-
MySQL统计函数记录
-
MySQL中查询、删除重复记录的方法大全
-
MySQL日期加减函数详解
-
MySql计算两个日期的时间差函数_MySQL
-
7、使用WHERE子句查询表中满足条件的记录_MySQL
-
利用mysql的inet_aton()和inet_ntoa()函数存储IP地址的方法分享_MySQL
-
查看mysql是否存在cmdshell这样的危险函数_MySQL
-
Linux 下 MySQL 5.5.8 源码编译安装记录
-
MySQL查询同个表中不同分类的前几条记录_MySQL
-
php中使用session_set_save_handler()函数把session保存到MySQL数据库实例,sessionhandler