如何在MySQL中选择包含特定值的两个连续行？

Question

我正在构建一个系统，应该显示学生连续缺席两天的情况。例如，这个表格包含了缺勤情况。

day         |   id  |   missed
----------------------------------
2016-10-6   |   1   |   true
2016-10-6   |   2   |   true
2016-10-6   |   3   |   false

2016-10-7   |   1   |   true
2016-10-7   |   2   |   false
2016-10-7   |   3   |   true

2016-10-10  |   1   |   false
2016-10-10  |   2   |   true
2016-10-10  |   3   |   true

在上述情况下： 学生1错过了第1天和第2天（连续）。 学生2错过了第1天和第3天（非连续）。 学生3错过了第2天和第3天（连续）。 查询应仅选择学生1和3。 是否可能只使用一个SQL查询完成这样的工作？

- Daniel Santos

Answer 1

使用内连接将两个表实例连接起来- 一个是“第一天”，另一个是“第二天”，然后只查找两者都缺失的行：

select a.id from yourTable as a inner join yourTable as b 
  on a.id = b.id and a.day = b.day-1 
  where a.missed = true and b.missed = true

编辑

既然您更改了规则...将日期而不是int作为day列，这就是我要做的：

1. Use DAYOFWEEK() function to go to a day as a number
2. Filter out weekends

3. use modulo to get Sunday as the next day of Thursday:

   select a.id from yourTable as a inner join yourTable as b 
     on a.id = b.id and DAYOFWEEK(a.day) % 5 = DAYOFWEEK(b.day-1) % 5 
     where a.missed = true and b.missed = true
     and DAYOFWEEK(a.day) < 6 and DAYOFWEEK(b.day) < 6
   

- Nir Levy

---

你没有按照学生ID进行连接。 - Iłya Bursov

这比它稍微复杂一些。我刚刚编辑了问题。 - Daniel Santos

Answer 2

类似于其他答案的方法，但语法不同。

select distinct id
from t
where
    missed=true and
    exists (
        select day
        from t as t2
        where t.id=t2.id and t.day+1=t2.day and t2.missed=true
    )

- Iłya Bursov

---

这比它稍微复杂一些。我刚刚编辑了问题。 - Daniel Santos

@DanielSantos 然后您需要添加 OR 条件，例如 ( (t.day+1=t2.day) or (DAYOFWEEK(t.day) == 6 AND t.day+3=t2.day) )。 - Iłya Bursov

Answer 3

这将为您提供每次发生的实例。如果他们连续缺席3天或更多，您将获得多个匹配项，所以如果这是一个问题，那么您需要进行进一步的优化。

SELECT
    T1.id,
    T1.day
FROM
    My_Table T1
INNER JOIN My_Table T2 ON
    T2.id = T1.id AND
    T2.day = T1.day + 1 AND
    T2.missed = true
WHERE
    T1.missed = true

- Tom H

---

它比这个稍微复杂一些。我刚编辑了这个问题。 - Daniel Santos

Answer 4

你可以使用变量来实现：

SELECT DISTINCT id
FROM (
  SELECT day, id, missed,
         @rn := IF(@id = id, 
                   IF(missed = true, @rn + 1, 0),
                   IF(@id := id, 
                      IF(missed = true, 1, 0), 
                      IF(missed = true, 1, 0))) AS rn                          
  FROM mytable
  CROSS JOIN (SELECT @rn := 0, @id := 0) AS var
  ORDER BY id, day) AS t
WHERE t.rn >= 2

以上方法的好处是可扩展性强：可以轻松地扩展以检查超过2个连续的学生缺勤记录。 此处演示

- Giorgos Betsos

---

这比它稍微复杂一些。我刚刚编辑了问题。 - Daniel Santos

Answer 5

这是一种方法...

SELECT x.id
  FROM my_table x
  JOIN my_table y
    ON y.id = x.id 
   AND y.missed = x.missed
   AND y.day > x.day
   AND 5 * (DATEDIFF(y.day, x.day) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(x.day) + WEEKDAY(y.day) + 1, 1) <= 1
 WHERE x.missed = 1;

- Strawberry

---

你能解释一下最后一个 AND 吗？ - Daniel Santos

@DanielSantos https://dev59.com/5XI-5IYBdhLWcg3wfoW1 - Strawberry