一种实现方法是通过如下所示的cte，其中我们具有row_number（）函数来双向跟踪序列。
看
**[live demo](http://rextester.com/VYAJ64788)**
; with cte as (select *, rn= row_number() over(partition by CustName order by RecordedTime),rn2=row_number() over(partition by CustName order by RecordedTime desc)from records ), cte2 as(select *, audit='New', History='Change' from cte where rn=1 union select *, audit='Change', History='Current' from cte where rn2=1 and rn<>1 unionselect *, audit='Change', History='before' from cte where rn>1 and rn2<>1)select CustName, Country, RecordedTime, audit, Historyfrom cte2order by CustName,RecordedTime
