SQL题目根据其他表，修改一个表的字段的值

根据其他表，修改一个表的字段的值
table1 字段和数据为：
A-----B-----C
a1---b1----c1
a2---b2----c2
a3---b3----c3
table2字段和数据为：
A----D
a1---d1
a2---d2
a3---d3

table3字段和数据为：
B-----e
b1----e1
b2----e2
b3----e3

执行sql后table1字段和数据变为：A-----B-----C
a1---b1----(d1+e1)
a2---b2----(d2+e2)
a3---b3---(d3+e3)

这条SQL语句应该怎么写呢？

if exists (select * from sysobjects where name='test')
drop table test
go
create table test(
A varchar(50),
B varchar(50),
C varchar(50)
)
go
INSERT INTO test SELECT distinct dbo.Table_3.e+dbo.Table_2.D as C, dbo.Table_1.A as A, dbo.Table_1.B as B
FROM dbo.Table_3 CROSS JOIN
dbo.Table_1 CROSS JOIN
dbo.Table_2
select * from test
额 希望你满意

如此即可

update t1 set t1.C=t2.D+'+'+t3.E from table1 t1, table2 t2,table3 t3 where t1.A=t2.A and t1.B = t3.B

我连带测试代码一块贴出来了，黑体部分是更新代码，你自己改下表名： declare @t1 table (A int, B int, C int) insert @t1 select 1, 1, 1 union select 2, 2, 2 union select 3, 3, 3 declare @t2 table (A int, D int) insert @t2 select 1, 1 union select 2, 3 union select 3, 5 declare @t3 table (B int, E int) insert @t3 select 1, 2 union select 2, 4 union select 3, 6 select * from @t1 select * from @t2 select * from @t3 update t1 set t1.C = ( select t2.D + t3.E from @t2 t2, @t3 t3 where t1.A = t2.A AND t1.B = t3.B ) from @t1 t1 select * from @t1 结果： A B C ----------- ----------- ----------- 1 1 1 2 2 2 3 3 3 (3 row(s) affected) A D ----------- ----------- 1 1 2 3 3 5 (3 row(s) affected) B E ----------- ----------- 1 2 2 4 3 6 (3 row(s) affected) (3 row(s) affected) A B C ----------- ----------- ----------- 1 1 3 2 2 7 3 3 11

update table1 set table1.C=table2.D + table3.e where table1.A=table2.A and table1.B=table3.B

试试用我这句吧~

UPDATe TABLE1 T SET C = (SELECT D + E FROM TABLE2, TABLE3, TABLE1 T2 WHERe TABLE2.A = TABLE1.A AND TABLE1.B = TABLE3.B AND T.A = T2.A)

如以上回答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息，可以点下面链接进行举报！

点此我要举报以上问答信息