本人现有2张表sheet1 sheet2,2张表内容如下sheet1 id cs ps sheet2 id cs ps
1701 103.2 9.4 1701 409 7.4
2046 246.67 8 2046 96.4 8.9
3952 365 76.3 3952 245.6 5
要实现将sheet1里面的cs列和ps列的内容改为2表相应列内容之和,2表可以通过id关联,这个语句应该怎么写,请各位前辈指教,之前试过update命令但都是在同一张表内,不通表之间就实现不了了!
1701 103.2 9.4 1701 409 7.4
2046 246.67 8 2046 96.4 8.9
3952 365 76.3 3952 245.6 5
要实现将sheet1里面的cs列和ps列的内容改为2表相应列内容之和,2表可以通过id关联,这个语句应该怎么写,请各位前辈指教,之前试过update命令但都是在同一张表内,不通表之间就实现不了了!
ON A.ID=B.ID
还得求和呀,题目都看错了!!
set cs=sheet1.cs+t.cs
,ps=sheet1.ps+t.ps
from sheet2 t
where sheet1.id=t.id
1701 103.2 409 9.4 7.4
2046 246.67 96.4 8 8.9
3952 365 245.6 76.3 5
是不是还和数据类型有关系吗我的cs和ps的数据类型是char
declare @sheet1 table
(
id int
,cs char(7)
,ps char(7)
);
declare @sheet2 table
(
id int
,cs char(7)
,ps char(7)
);
insert into @sheet1
values(1701,103.2,9.4)
,(2046,246.67,8)
,(3952,365,76.3);
insert into @sheet2
values(1701,409,7.4)
,(2046,96.4,8.9)
,(3952,245.65,76.3);
--更新
update @sheet1
set cs=cast(cast(t1.cs as decimal(5,2))+cast(t2.cs as decimal(5,2)) as CHAR(7))
,ps=cast(cast(t1.ps as decimal(5,2))+cast(t2.ps as decimal(5,2)) as CHAR(7))
from @sheet1 t1
join @sheet2 t2 on t1.id=t2.id
update sheet1 set cs=cast((cast(a.cs as float)+cast(b.cs as float)) as char),ps=cast((cast(a.ps as float)+cast(b.ps as float)) as char) from sheet1 a join sheet2 b on a.id=b.idselect * from sheet1
--------------------
id cs ps
1701 512.2 16.8
2046 343.07 16.9
3952 310.6 11.3