MySQL计数行在多个日期范围内？ [英] MySQL count rows in multiple date ranges?

问题描述

我想计算来自给定表格的多个日期范围（即：最近一小时，今天，本周，过去30天）的行数。

以知道在该时间/日期时间段中有多少条目能够判断给定用户是否已经达到这些范围中的每一个的限制。例如，用户最多可以有300个条目一个月，但是有（每小时/每日/每周/每月）限制。

到目前为止，我尝试使用子查询方法使用类似于此的 SELECT CASE ：

这是最好的方法吗？ >解决方案

在mysql中，您可以使用一系列计数函数与if语句，以便只计算所需的日期。

  SELECT COUNT（IF（date> = DATE_SUB（NOW（），INTERVAL 1 HOUR），1，null））AS hourHits，
  pre> 
 
 
等等
 
 
 
   strong> 
 
I want to count the rows in several date ranges (i.e: last hour, today, this week, last 30 days) from a given table.

I need to know how many entries are in this time/date periods to be able to tell if a given user has reach the limit for each one of this ranges. For instance, a user can have max 300 entries one month but with a (hourly/daily/weekly/monthly) limit.

So far I'm trying with a subquery approach using a SELECT CASE similar to this one: group by range in mysql

Which should be the best way of doing this?
解决方案
In mysql you could use a series of count functions with if statements so that only the required  dates are counted, like so.
SELECT COUNT(IF(date >= DATE_SUB(NOW(), INTERVAL 1 HOUR), 1, null)) AS hourHits, 
and so on

Edited as per comments

                        
这篇关于MySQL计数行在多个日期范围内？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！