sql - 仅包括事件顺序有效的分组观察

Question

我有一张个人眼科检查和眼镜购买日期表。我只想保留个人在眼科检查后购买眼镜的情况。在下面的示例中，我希望为人员 2、人员 3 保留人员 1、事件 2 和 3，但不保留人员 4。如何在 SQL Server 中执行此操作？

| Person | Event  | Order |
|    1   |  Exam  |    1  |
|    1   | Eyewear|    2  | 
|    2   | Eyewear|    1  |
|    2   | Exam   |    2  | 
|    2   | Eyewear|    3  |
|    3   | Exam   |    1  | 
|    3   | Eyewear|    2  |
|    4   | Eyewear|    1  | 
|    4   | Exam   |    2  |

最终结果看起来像

| Person | Event  | Order |
|    1   |  Exam  |    1  |
|    1   | Eyewear|    2  | 
|    2   | Exam   |    2  | 
|    2   | Eyewear|    3  |
|    3   | Exam   |    1  | 
|    3   | Eyewear|    2  |

Answer 1

自加入应该工作...

select
    t.Person
    ,t.Event
    ,t.[Order]
from
    yourTable t
    inner join
    yourTable t2 on t2.Person = t.Person
    and t2.[Order] = (t.[Order] +1)
where
    t2.Event = 'Eyewear'
    and t.Event = 'Exam'