mysql按时间段统计例子

发表于 | 分类于 | |

mysql按时间段统计（年，季度，月，天，时）

按年汇总，统计：

1
select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y');

按月汇总，统计：

1
select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y-%m');

按季度汇总，统计：

1
select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));
1
select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));

按小时：

1
select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by date_format(col, '%Y-%m-%d %H ');

查询本年度的数据:

1
SELECT * FROM mytable WHERE year(FROM_UNIXTIME(my_time)) = year(curdate())

查询数据附带季度数:

1
SELECT id, quarter(FROM_UNIXTIME(my_time)) FROM mytable;

查询本季度的数据:

1
SELECT * FROM mytable WHERE quarter(FROM_UNIXTIME(my_time)) = quarter(curdate());

本月统计:

1
select * from mytable where month(my_time1) = month(curdate()) and year(my_time2) = year(curdate())

本周统计:

1
select * from mytable where month(my_time1) = month(curdate()) and week(my_time2) = week(curdate())

N天内记录:

1
WHERE TO_DAYS(NOW())-TO_DAYS(时间字段)<=N
1
2
3
4
5
6
7
8
9
10
11
12
SELECT
	COUNT(u.id) AS total,
	DATE_FORMAT(
		FROM_UNIXTIME(u.reg_time),
		'%Y-%m-%d'
	) AS date
FROM
	apps_user AS u
WHERE
	FROM_UNIXTIME(u.reg_time) >= DATE_ADD(NOW(), INTERVAL - 7 DAY)
GROUP BY
	date

本篇文章来源于Linux 公社网站