表 a:
UUID APP_LINE_ID
87219 513131
表 ca:
ASSET_ID APP_LINE_TF_ID APP_LINE_TK_ID
87219 494386 0
87219 513131 0
表 ar:
RELATION_ID ASSET_ID EVENT_TYPE
494386 87219 1507
494386 87219 1507
513198 87219 1508
513198 87219 1508
513131 87219 1507
513131 87219 1507
表间关系说明：
1.表a的uuid是表a是主键，同时作为表ar与ca的外键
2.表ca的APP_LINE_TF_ID，APP_LINE_TK_ID，表 ar的 RELATION_ID 实质都是表a的APP_LINE_ID，只是表a只记录最后一次发生的 APP_LINE_ID
3.ar中 EVENT_TYPE = 1507 的要对应表ca中 APP_LINE_TF_ID
4.ar中 EVENT_TYPE = 1508 的要对应表ca中 APP_LINE_TK_ID
以上数据有异常，需要的结果是让表ar变成如下结果：
ASSET_ID APP_LINE_TF_ID APP_LINE_TK_ID
87219 494386 513198
87219 513131 0
------解决方案--------------------
表不少，怎么连个主键都没有？
- SQL code
declare @表a table (UUID int,APP_LINE_ID int)insert into @表aselect 87219,513131declare @表ca table (ASSET_ID int,APP_LINE_TF_ID int,APP_LINE_TK_ID int)insert into @表caselect 87219,494386,0 union allselect 87219,513131,0declare @表ar table (RELATION_ID int,ASSET_ID int,EVENT_TYPE int)insert into @表arselect 494386,87219,1507 union allselect 494386,87219,1507 union allselect 513198,87219,1508 union allselect 513198,87219,1508 union allselect 513131,87219,1507 union allselect 513131,87219,1507select * from @表aselect * from @表caselect * from @表ar
------解决方案--------------------
看了一上午,还是不太明白楼主什么意思,猜测楼主的需求代码写出来了,看看吧~
- SQL code
declare @表a table (UUID int,APP_LINE_ID int)insert into @表aselect 87219,513131declare @表ca table (ASSET_ID int,APP_LINE_TF_ID int,APP_LINE_TK_ID int)insert into @表caselect 87219,494386,0 union allselect 87219,513131,0declare @表ar table (RELATION_ID int,ASSET_ID int,EVENT_TYPE int)insert into @表arselect 494386,87219,1507 union allselect 494386,87219,1507 union allselect 513198,87219,1508 union allselect 513198,87219,1508 union allselect 513131,87219,1507 union allselect 513131,87219,1507select * from @表aselect * from @表caselect * from @表arupdate @表ca set APP_LINE_TF_ID=(select top 1 relation_id from @表ar where EVENT_TYPE='1507') where APP_LINE_TF_ID!=(select APP_LINE_ID from @表a)update @表ca set APP_LINE_TK_ID=(select top 1 relation_id from @表ar where EVENT_TYPE='1508') where APP_LINE_TF_ID!=(select APP_LINE_ID from @表a)select * from @表ca