MySQL统计函数记录——时间段统计
MySQL统计函数记录——时间段统计
七律断舍离 发表于5个月前
MySQL统计函数记录——时间段统计
  • 发表于 5个月前
  • 阅读 5
  • 收藏 1
  • 点赞 0
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

  • 按年汇总,统计:
    • 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
标签: MySQL curdate
共有 人打赏支持
粉丝 6
博文 40
码字总数 15483
×
七律断舍离
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: