mysql - 如果后面的记录符合条件，则返回 id 的结果

Question

样本数据：

date   id   case   type    case_review_date
11/1   1    90     Review  11/1
12/27  1    23     Block
1/1    1    12     Review  1/1
1/2    1    45     Failed
1/3    1    78     Review  1/3
1/3    3    62     Failed
1/1    2    33     Review  1/1
1/10   2    63     Failed

目标是仅返回审核日期后 7 天内 id 类型为 Failed 的行。所以对于这个例子，它应该只返回 id 1 case 12。使用 MySQL。很难思考这个问题的逻辑。非常感谢任何帮助！

尝试了以下操作，它似乎无法正常工作，或者根据 ID 将其隔离。相反，如果在 7 天内有任何 Failed 案例，它会返回所有 Review 条目，无论它是否用于相同的 ID。所以它返回以下内容：

date   id   case   type    case_review_date
1/1    1    12     Review  1/1
1/3    3    62     Failed

select *
from casetable
where exists
   (select 1
    from casetable
    where casetable.type = 'Failed' and DATEDIFF(day, casetable.date, casetable.case_review_date) <= 7)

标签： mysqlfiltercasewhere-clause

Answer 1

您可以在此处使用存在逻辑：

SELECT date, id, `case`, type
FROM yourTable
WHERE
    type = 'Review' AND
    NOT EXISTS (SELECT 1 FROM yourTable t2
                WHERE t2.id = t1.id AND t2.type = 'Review' AND
                      t2.date < t1.date) AND
    EXISTS (SELECT 1 FROM yourTable t2
            WHERE t2.id = t1.id AND t2.type = 'Failed' AND
                  DATEDIFF(t2.date, t1.date) <= 7);