MySQL:如何从某个日期算起 1 周

Question

我有下表:

create table my_table
(
 SubjectID int,
 Date Date,
 Test_Value int
);

insert into my_table(SubjectID, Date, Test_Value)
values
(1, '2014-01-01', 55),
(1, '2014-01-05', 170),
(1, '2014-01-30', 160),
(2, '2014-01-02', 175),
(2, '2014-01-15', 40),
(2, '2014-01-18', 30),
(2, '2014-01-20', 166),
(3, '2014-01-05', 100),
(3, '2014-01-07', 75),
(3, '2014-01-11', 180),
(3, '2014-01-21',90)

我正在尝试识别 Test_Value >=160 并且在他/她得分为 160 后的 7 天内进行了 2 次或更多次测试的 SubjectID。例如，SubjectID 1 不符合此条件。当他在“2014-1-30”上获得 160 分时，这两次先前的测试是在一个多星期前进行的。 SubjectID 2 满足此条件，因为他在“2014-01-20”上获得了 166 分，之前的两次测试是在一周内进行的。 SubjectID 3 通过在“2014-01-11”上获得 180 分并且在一周内进行过两次先前的测试也满足条件。

澄清一下，在获得 160 分之前两次或更多次的考试成绩并不重要。他们必须在受试者得分 160 之日起一周内才能满足条件。

到目前为止，我已经编写了这段代码:

set @test = 0, @id=0, @count=0;


select 
 @count := if(SubjectID = @id, @count+1, 0) as count,
 @test :=  if(Test_Value >= 160, 1,  0 ) as Test_Index,
 @id := SubjectID as id
from my_table 

我知道这段代码没有给我答案，但如果我能将一周的相对时间范围加入这段代码，问题就可以解决。我不确定上面的代码是一个好的起点。您能否在此代码中添加一些内容或编写一组完全不同的代码？提前感谢您的帮助。

Answer 1

我认为这可以满足您的需求，无需变量:

select s160.*
from my_table s160 join
     my_table sprev
     on s160.test_value >= 160 and
        s160.SubjectId = sprev.SubjectId and
        sprev.Date between s160.Date - interval 7 day and s160.Date
group by s160.SubjectId, s160.Date, s160.Test_Value
having count(*) >= 3;

count(*) >= 3 是因为计数也包括得分较高的测试。如果此表具有在所有行上唯一的实际 ID，将会很有帮助。