; WITH tmp AS ( SELECT SUM(数量) qty , number FROM 表1 GROUP BY number , 数量 ) UPDATE 表1 SET 表1.数量 = tmp.qty FROM tmp WHERE tmp.number = 表1.表1
update a set a.数量=b.数量 from 表2 a,(select sum(数量) 数量,number from 表1 group by number) b where a.number=b.number
update a set 数量=b.数量 from 表2 a, (select sum(数量) 数量,number from 表1 group by number) b where a.[number]=b.[number]
drop table #tb1 drop table #tb2 create table #tb1 (number int ,数量 int) insert into #tb1 values(1,1) insert into #tb1 values(2,2) insert into #tb1 values(3,3) insert into #tb1 values(4,4) insert into #tb1 values(5,5)
create table #tb2 (number int,数量 int ) insert into #tb2 values(1,6) insert into #tb2 values(2,7) insert into #tb2 values(3,8) insert into #tb2 values(4,9) insert into #tb2 values(5,10) update a set 数量=t.数量 from #tb2 a, ( select sum(数量) as 数量,number from #tb1 group by number,数量 ) t where a.[number]=t.[number]
update 表2 set 数量=b.数量 from 表2 inner join (select 数量=sum(数量),number from 表1 group by number) b on 表2.number=b.number
WITH tmp
AS ( SELECT SUM(数量) qty ,
number
FROM 表1
GROUP BY number ,
数量
)
UPDATE 表1
SET 表1.数量 = tmp.qty
FROM tmp
WHERE tmp.number = 表1.表1
from 表2 a,(select sum(数量) 数量,number from 表1 group by number) b where a.number=b.number
a
set
数量=b.数量
from
表2 a,
(select sum(数量) 数量,number from 表1 group by number) b
where
a.[number]=b.[number]
drop table #tb2
create table #tb1
(number int ,数量 int)
insert into #tb1 values(1,1)
insert into #tb1 values(2,2)
insert into #tb1 values(3,3)
insert into #tb1 values(4,4)
insert into #tb1 values(5,5)
create table #tb2
(number int,数量 int )
insert into #tb2 values(1,6)
insert into #tb2 values(2,7)
insert into #tb2 values(3,8)
insert into #tb2 values(4,9)
insert into #tb2 values(5,10)
update
a
set
数量=t.数量
from
#tb2 a,
(
select sum(数量) as 数量,number from #tb1 group by number,数量
) t
where
a.[number]=t.[number]
update 表2 set 数量=b.数量 from 表2 inner join (select 数量=sum(数量),number from 表1 group by number) b
on 表2.number=b.number
sum不是返回这个字段的数字之和吗? 用在这里起什么作用呢?