按年/月/日统计数据
程序员文章站
2024-03-25 21:18:52
...
按天统计数据
select date_format(create_date,'%Y-%m-%d') as '日期' , sum(amount) as '收入' from income group by date_format(create_date,'%Y-%m-%d');
按月统计
select date_format(create_date,'%Y-%m') as '日期' , sum(amount) as '收入' from income group by date_format(create_date,'%Y-%m');
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
上一篇: 视觉SLAM理论与实践3
下一篇: MySql存储过程的使用
推荐阅读