sql - 比较相同 ID 的两个最近的列

Question

我有一张个人地址表。 Class 列表明它是住宅 (H)、邮政 (P) 还是内部 (I)地址。每次有人更新地址时，日期也会被存储。

当由于地址不正确而将信件退回给我们时，我公司会使用内部地址，因此我们会将其更新为我们的地址，直到我们可以获得新的联系方式为止。

我想知道哪些人将他们的地址更改为邮政地址或家庭住址，其生效日期晚于内部地址的生效日期。

这是表格的示例:

| **PersonID**| **Class** | **EffDate** | **Line1**
| 1           | H         | 12/01/2010  | 31 Academy Avenue 
| 1           | H         | 13/09/2010  | 433 Hillcrest Drive 
| 1           | I         | 26/10/2015  | 1 Bond Circle
| 2           | H         | 17/12/2012  | 761 Circle St. 
| 2           | H         | 12/11/2013  | 597 Elm Lane 
| 2           | I         | 1/10/2015   | 1 Bond Circle
| 2           | H         | 6/12/2016   | 8332 Mountainview St. 
| 3           | P         | 27/09/2010  | 8 Bow Ridge Lane 
| 3           | H         | 6/12/2010   | 22 Shady St. 
| 3           | I         | 7/12/2015   | 1 Bond Circle
| 3           | H         | 8/12/2016   | 7423 Rockcrest Ave. 
| 4           | P         | 9/12/2015   | 888 N. Shady Street 
| 4           | I         | 10/12/2016  | 1 Bond Circle

我希望查询只返回:

| **PersonID**| **Class** | **EffDate** | **Line1**
| 2           | H         | 6/12/2016   | 8332 Mountainview St.
| 3           | H         | 8/12/2016   | 7423 Rockcrest Ave. 

有什么想法吗？我正在使用 SQL Server 2012。

Answer 1

这是一种方法:

select t.*
from t
where t.class in ('H', 'P') and
      t.effdate > (select max(t2.effdate)
                   from t t2
                   where t2.class = 'I' and t2.personId = t.personId
                  );

另一种使用窗口函数的方法:

select t.*
from (select t.*,
              max(case when class = 'I' then date end) over (partition by personid) as max_idate
      from t
     ) t
where t.class in ('H', 'P') and
      t.date > t.maxidate;