字段批改日志对比

字段修改日志对比
如下表档案记录
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计算过程！ 


[解决办法]

SQL code

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' unionselect 3,'001','C1-8','B','2012-02-05' unionselect 4,'001','C1-9','B','2012-02-08' unionselect 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.IDunion 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)twhere ROW=1------------DTIME                   CODE FIELD OLD        NEW----------------------- ---- ----- ---------- ----------2012-02-05 00:00:00.000 001  NAME  C1         C1-82012-02-05 00:00:00.000 001  TYPE  A          B2012-02-08 00:00:00.000 001  NAME  C1-8       C1-92012-02-09 00:00:00.000 002  NAME  C2         C2-12012-02-09 00:00:00.000 002  TYPE  B          C