mysql - 当数字 = 0 或 > 10 时，计算连续 10 天的天数

Question

这是我用 mysql 查询制作的 sqlfiddle http://sqlfiddle.com/#!2/f2794/4

当present = 0时，它会计算连续10天，但我需要添加第二个条件来计算present> 10的情况。

例如

11220000000000001

应该数到14

这是该查询

select sum(count) total from (

SELECT COUNT(present) as count FROM (
SELECT
IF((q.present != 0), @rownum:=@rownum+1, @rownum:=@rownum) AS rownumber, @prevDate:=q.date, q.*
FROM (
SELECT
name
, date
, present
FROM
teacher, (SELECT @rownum:=0, @prevDate:='') vars
WHERE date BETWEEN '2013-07-01' AND '2013-07-31'
ORDER BY date, present
) q
) sq
GROUP BY present, rownumber
HAVING COUNT(*) >= 10

) d

所以如果你能帮助我，请帮助我:)

最诚挚的问候米。

Answer 1

我不太理解您的查询，但我认为简单地更改 (q.Present != 0) 以合并附加测试应该可以解决您的问题:

SELECT sum(count) total from (
  SELECT COUNT(present) as count FROM (
    SELECT
      IF((q.present != 0 AND q.present <= 10), @rownum:=@rownum+1, @rownum:=@rownum) AS rownumber, @prevDate:=q.date, q.*
    FROM (
      SELECT
        name
        , date
        , present
      FROM
        teacher, (SELECT @rownum:=0, @prevDate:='') vars
      WHERE date BETWEEN '2013-07-01' AND '2013-07-31'
      ORDER BY date, present
    ) q
  ) sq
  GROUP BY present, rownumber
  HAVING COUNT(*) >= 10
) d