如下图表为t_FR
ID | FRID | SID | PID | LastChangedDate | StartDate | EndDate |
1 | 1 | 2 | 2 | 2010-10-01 | 2009-10-01 | 2008-10-01 |
2 | 1 | 1 | 1 | 2008-10-01 | 2009-10-01 | 2008-10-01 |
3 | 1 | 2 | 2 | 2008-12-01 | 2009-10-01 | 2008-10-01 |
4 | 2 | 4 | 3 | 2009-10-01 | 2009-10-01 | 2008-10-01 |
5 | 2 | 4 | 3 | 2009-10-01 | 2009-10-01 | 2008-10-01 |
6 | 3 | 6 | 4 | 2010-10-01 | 2009-10-01 | 2008-10-01 |
7 | 3 | 6 | 4 | 2009-11-01 | 2009-10-01 | 2008-10-01 |
8 | 4 | 6 | 4 | 2010-10-05 | 2009-10-01 | 2008-10-01 |
用sql实现按照FRID,SID及PID分组中LastChangeDate为最新的一条记录。
Select * from t_FR t wher t.ID in (select top 1 ID from t_FR where FRID=t.FRID and SID=t.SID and PID=t.PID order by LastChangedDate desc)
结果为:
ID | FRID | SID | PID | LastChangedDate | StartDate | EndDate |
1 | 1 | 2 | 2 | 2010-10-01 | 2009-10-01 | 2008-10-01 |
5 | 2 | 4 | 3 | 2009-10-01 | 2009-10-01 | 2008-10-01 |
7 | 3 | 6 | 4 | 2009-11-01 | 2009-10-01 | 2008-10-01 |
8 | 4 | 6 | 4 | 2010-10-05 | 2009-10-01 | 2008-10-01 |