1. 爱易网页
  2. MSSQL教程
  3. 求一多对多关系下的更新语句！该如何解决

求一多对多关系下的更新语句！该如何解决

日期：2014-05-18　 浏览次数：20605 次

求一多对多关系下的更新语句！
表 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 @表a
select 87219,513131

declare @表ca table (ASSET_ID int,APP_LINE_TF_ID int,APP_LINE_TK_ID int)
insert into @表ca
select 87219,494386,0 union all
select 87219,513131,0

declare @表ar table (RELATION_ID int,ASSET_ID int,EVENT_TYPE int)
insert into @表ar
select 494386,87219,1507 union all
select 494386,87219,1507 union all
select 513198,87219,1508 union all
select 513198,87219,1508 union all
select 513131,87219,1507 union all
select 513131,87219,1507

select * from @表a
select * from @表ca
select * from @表ar

------解决方案--------------------

看了一上午,还是不太明白楼主什么意思,猜测楼主的需求代码写出来了,看看吧~
SQL code
declare @表a table (UUID int,APP_LINE_ID int)
insert into @表a
select 87219,513131

declare @表ca table (ASSET_ID int,APP_LINE_TF_ID int,APP_LINE_TK_ID int)
insert into @表ca
select 87219,494386,0 union all
select 87219,513131,0

declare @表ar table (RELATION_ID int,ASSET_ID int,EVENT_TYPE int)
insert into @表ar
select 494386,87219,1507 union all
select 494386,87219,1507 union all
select 513198,87219,1508 union all
select 513198,87219,1508 union all
select 513131,87219,1507 union all
select 513131,87219,1507

select * from @表a
select * from @表ca
select * from @表ar

update @表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

相关资料更多>