如何使用MySQL选择具有特定值的连续2行？ [英] How to select where there are 2 consecutives rows with a specific value using MySQL?

问题描述

我正在建立一个系统，当学生连续两天错过时应该显示。
例如，此表包含缺勤。

  day | id |错过了
 ---------------------------------- 
 2016-10-6 | 1 |真
 2016-10-6 | 2 |真
 2016-10-6 | 3 |假
 
 2016-10-7 | 1 |真
 2016-10-7 | 2 |假
 2016-10-7 | 3 |真
 
 2016-10-10 | 1 |假
 2016-10-10 | 2 |真
 2016-10-10 | 3 | true 
  

（天数2016-10-8和2016-10-9在上述情况下：

• 学生1错过了第一和第二天。 （连续）


• 学生2错过了第一天和第三天。 （不连续）


• 学生3错过了第二和第三天。 （连续的）

查询应该只选择学生1和3。

使用单个SQL查询可以完成这样的工作吗？使用内部连接来连接表的两个实例

- 一个'第一'天，一个'第二'天，然后只查找两行都被忽略的行：

 从yourTable中选择a.id作为内部连接yourTable作为b 
在a.id = b.id和a.day = b.day-1 
其中a.missed = true和b .missed = true 
  

编辑

现在你已经改变了规则......并且在日期栏中设置了日期而不是int，这就是我要做的事情：

1. 使用 DAYOFWEEK （）函数作为一个数字前往一天


2. 过滤掉周末



3. 使用modulo来获取周日的第二天：
   
   

    从yourTable中选择a.id作为内部联接yourTable在a.id = b.id和DAYOFWEEK（a.day）％5 = DAYOFWEEK（b.day-1）％5 
   其中a.missed = true和b.missed = true时为b 
    
   和DAYOFWEEK（a.day）< 6和DAYOFWEEK（b.day）< 6 
     

I'm building a system that should show when the students missed two days in a row. For example, this table contains the absences.

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

(days 2016-10-8 and 2016-10-9 are weekend)

in the case above:

• student 1 missed the days 1st and 2nd. (consecutive)
• student 2 missed the days 1st and 3rd. (nonconsecutive)
• student 3 missed the days 2nd and 3rd. (consecutive)

The query should select only student 1 and 3.

Is possible to do stuff like this just with a single SQL Query?

解决方案

Use inner join to connect two instances of the table- one with the 'first' day, and one with the 'second' day, and then just look for rows where both are missed:

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

EDIT

Now that you changed the rules... and made it date and not int in the day column, this is what I'll do:

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
   

这篇关于如何使用MySQL选择具有特定值的连续2行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！