连续10天怎么算 [英] How to count consecutive number of 10 days

问题描述

我有包含列的表:id、name、date、present列存在的值为 0 或 1 或 2 和 ... 更多我需要计算当月 2013-07-01 - 2013-07-31 有多少个 0 valous 但只有在有或超过 10 次时才计算.

I have table with columns: id, name, date, present Column present have values 0 or 1 or 2 and ... more I need to count how many 0 valous is in current month 2013-07-01 - 2013-07-31 but count only when there are or more than 10 times.

例如，如果我有2013-07-01 到 2013-07-10 值 0 应该算它，让我知道连续 10 天或更多天，如 11、12 或更多，但如果它小于 10 应该算不了什么.

for example if i have 2013-07-01 to 2013-07-10 valoues 0 it should count it and let me know that is 10 or more consecutives days like 11, 12 or more, but if it was less than 10 should count nothing.

我正在尝试堆栈中的一些示例...但它们是不同的问题...所以我几乎不需要有关该 mysql 查询的帮助.

I was trying some examples from stack... but they are different problems... so i need little help with that mysql query.

我有这样的经历，但需要连续 10 天，例如 >= 10

i have smth like this but need consecutives 10 days like >= 10

$sql = mysql_query("SELECT COUNT(name) as count FROM `table` WHERE (`present` = 0) AND (`date` BETWEEN '2013-07-01' AND '2013-07-31')");

while($row = mysql_fetch_array($sql)){
    $result = $row['count'];
}

它计算我在 2013-07-01 和 2013-07-31 之间的日期中的每 0 个值，但我需要计算从 10 天或更多连续天开始的天数

It counts me every 0 values in date between 2013-07-01 and 2013-07-31 but i need count how many days start from 10 or more consecutives days

当前列有 0 和其他数字，如 1、2、3...所以我只需要计算 10 天或更多连续天数的 0

column present have 0 and other numbers like 1, 2, 3... so i need count only 0 with 10 or more consecutives days

这里是 SqlFiddle，我试图从答案中发出警告http://sqlfiddle.com/#!2/1bde8/2

here is SqlFiddle i was trying to make warking from answer http://sqlfiddle.com/#!2/1bde8/2

最好的问候米.

推荐答案

这种方法使用相关子查询来计算两个值.

This approach uses correlated subqueries to calculate two values.

第一个值是上一条记录的日期，其中 Present = 1.这允许您使用 datediff() 获得 Present = 0 的连续天数.

The first value is the date of the previous record where Present = 1. This allows you to get the number of days in a row where Present = 0 by using datediff().

第二个是明天的Present值，在当月的最后一天为NULL.当今天有 Present = 0 并且明天是 1 或 NULL 时，我们就可以使用这个记录.它是 0s 序列的结尾.

The second is the Present value of tomorrow, which will be NULL on the last day of the month. When today has Present = 0 and tomorrow is either 1 or NULL, then we can use this record. It is the end of a sequence of 0s.

这只是根据您设置的条件将值相加的问题.以下查询假定您要为每个 name 执行此操作:

From there is it just a question of adding up the values according to the conditions that you set. The following query assumes that you want to do this for each name:

select name, sum(case when datediff(date, lastPresentDate) >= 10
                      then datediff(date, lastPresentDate)
                 else 0 end) as DaysCounted
from (select t.*,
             (select coalesce(max(date), '2013-06-30')
              from t t2
              where t2.name = t.name and
                    t2.present <> 0 and
                    t2.date <= t.date and
                    t2.date between '2013-07-01' and '2013-07-31'
             ) as lastPresentDate,
             (select t2.present
              from t t2
              where t2.name = t.name and
                    t2.date = adddate(t.date, 1)
              order by t2.date
              limit 1
             ) as TomorrowPresent
      from t
      where date between '2013-07-01' and '2013-07-31'
     ) t
where Present = 0 and (TomorrowPresent = 1 and TomorrowPresent is null)
group by name

这篇关于连续10天怎么算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！