字段修改日志对比

[appleller]

如下表档案记录
TABLE:
ID CODE NAME TYPE DTIME
1 001 C1 A 2012-01-08
2 002 C2 B 2012-01-09
3 001 C1-8 B 2012-02-05
4 001 C1-9 B 2012-02-08
5 002 C2-1 C 2012-02-09

以CODE为依据对name各字段数据进行对比：
对比结果：
DTIME CODE FIELD OLD NEW
2012-02-05 001 NAME C1 C1-8
2012-02-05 001 TYPE A B
2012-02-08 001 NAME C1-8 C1-9
2012-02-09 002 NAME C2 C2-1
2012-02-09 002 TYPE B C

求SQL计算过程！

[Aaron_Chan]

declare @t table(ID int,Code char(3),[name] varchar(10),[type] char(1),dtime datetime)
insert into @t 
select 1,'001','C1','A','2012-01-08' union 
select 2,'002','C2','B','2012-01-09' union
select 3,'001','C1-8','B','2012-02-05' union
select 4,'001','C1-9','B','2012-02-08' union
select 5,'002','C2-1','C','2012-02-09' 
select DTIME,CODE,FIELD,OLD,NEW from(
select t2.DTIME,t2.CODE,FIELD='NAME',OLD=t1.[NAME],NEW=t2.[NAME],ROW=ROW_NUMBER() over(partition by t1.[NAME] order by t1.CODE) from @t t1 
join @t t2 on t1.CODE=t2.CODE and t1.[NAME]<>t2.[NAME] and t1.ID<t2.ID
union 
select t2.DTIME,t2.CODE,FIELD='TYPE',OLD=t1.[TYPE],NEW=t2.[TYPE],ROW=ROW_NUMBER() over(partition by t1.[TYPE] order by t1.CODE) from @t t1 
join @t t2 on t1.CODE=t2.CODE and t1.[TYPE]<>t2.[TYPE] and t1.ID<t2.ID)t
where ROW=1

------------
DTIME                   CODE FIELD OLD        NEW
----------------------- ---- ----- ---------- ----------
2012-02-05 00:00:00.000 001  NAME  C1         C1-8
2012-02-05 00:00:00.000 001  TYPE  A          B
2012-02-08 00:00:00.000 001  NAME  C1-8       C1-9
2012-02-09 00:00:00.000 002  NAME  C2         C2-1
2012-02-09 00:00:00.000 002  TYPE  B          C