比较相同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。

- Marcos O

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;

- Gordon Linoff