mysql汇总每天记录_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

-- 全站时段统计

SELECT tch.hourlist as hourStr,

IFNULL(tv.numStr,0) as viewCount,

IFNULL(ts.numStr,0) as shareCount,

IFNULL(tl.numStr,0) as likesCount,

IFNULL(tc.numStr,0) as collectCount

FROM

(SELECT hourlist from t_calendar_hour) as tch

LEFT JOIN (select DATE_FORMAT(CREATE_TIME,'%H') as hourStr, count(ID) as numStr from t_article_view_record where 1=1 and CREATE_TIME>='2016-06-20 00:00:00' and CREATE_TIME<='2016-06-20 23:59:59' GROUP BY DATE_FORMAT(CREATE_TIME,'%Y-%m-%d %H')) as tv

ON tv.hourStr = tch.hourlist

LEFT JOIN (select DATE_FORMAT(CREATE_TIME,'%H') as hourStr, count(ID) as numStr from t_article_share_record where 1=1 and CREATE_TIME>='2016-06-20 00:00:00' and CREATE_TIME<='2016-06-20 23:59:59' GROUP BY DATE_FORMAT(CREATE_TIME,'%Y-%m-%d %H')) as ts

ON ts.hourStr = tch.hourlist

LEFT JOIN (select DATE_FORMAT(CREATE_TIME,'%H') as hourStr, count(ID) as numStr from t_article_likes_record where 1=1 and CREATE_TIME>='2016-06-20 00:00:00' and CREATE_TIME<='2016-06-20 23:59:59' GROUP BY DATE_FORMAT(CREATE_TIME,'%Y-%m-%d %H')) as tl

ON tl.hou