MySQL统计函数记录——按月、按季度、按日、时间段统计

             

# 按年汇总,统计:

select sum(field), date_time from table_name group by date_format(date_time, '%Y');

# 按月汇总,统计:

select sum(field), date_time from table_name group by date_format(date_time, '%Y-%m');

# 按季度汇总,统计:

select sum(field), date_time from table_name group by concat(date_format(date_time, '%Y'),FLOOR((date_format(date_time, '%m')+2)/3));

# 按小时:

select sum(field), date_time from table_name group by date_format(date_format, '%Y-%m-%d %H ');

# 查询 本年度的数据:

SELECT * FROM table_name WHERE year(date_time) = year(curdate());

# 查询数据附带季度数:

SELECT id, quarter(date_time) FROM table_name;

# 查询 本季度的数据:

SELECT * FROM table_name WHERE quarter(date_time) = quarter(curdate());

# 本月统计:

select * from table_name where month(date_time) = month(curdate()) and year(date_time) = year(curdate());

# 本周统计:

select * from table_name where month(date_time) = month(curdate()) and week(date_time) = week(curdate());

# N天内记录:

select id, date_time, TO_DAYS(NOW()), TO_DAYS(date_time) from table_name WHERE TO_DAYS(NOW())-TO_DAYS(date_time) <= 5;


发表评论 请登录再评论
  •   文章分类
回到顶部