--追加B中不存在的
insert into B(hh,pm,sl)
select A.hh,A.pm,A.sl
from A left join B on A.hh=B.hh
where B.hh is null
--更新存在的
update B set sl=isnull(sl,0) + isnull(A.sl,0)
from A inner join B on A.hh=B.hh
insert into B(hh,pm,sl)
select A.hh,A.pm,A.sl
from A left join B on A.hh=B.hh
where B.hh is null
--更新存在的
update B set sl=isnull(sl,0) + isnull(A.sl,0)
from A inner join B on A.hh=B.hh
insert into B(hh,pm,sl)
select A.hh,A.pm,A.sl
from A
where hh not in (select hh from B)
--更新存在的
update B set sl=isnull(sl,0) + isnull(A.sl,0)
from A inner join B on A.hh=B.hh
select A.hh,A.pm,A.sl
from A left join B on A.hh=B.hh
where B.hh is null我的要求可以用一条语句完成吗?
--追加B中不存在的
insert into B
select A.*
from A left join B on A.hh=B.hh
where B.hh is null
--更新存在的
update B set sl=isnull(sl,0) + isnull(A.sl,0)
from A inner join B on A.hh=B.hh
update B set sl=isnull(sl,0) + isnull(A.sl,0)
from A inner join B on A.hh=B.hh
能我解释一下吗?sl=isnull(sl,0) + isnull(A.sl,0)
--如果两个表中的sl字段都不会为null,可以不用isnull()函数
--但是如果有为空的,必须用isnull()函数。--因为任何数值与null作算术运算都是nullupdate B set sl=isnull(sl,0) + isnull(A.sl,0)
from A
inner join B on A.hh=B.hh
declare @i int ,@j intset @i=100
--这里@j为null,所以作算术运算后,结果不是100,而是null
select @i+@j
--这里不论两个变量是否为null,如果为null,自动转为0,所以结果为100
select isnull(@i,0)+isnull(@j,0)
update B
set sl=isnull(sl,0) + sum(isnull(A.sl,0))--如果A存在一对多关系时sum
from A inner join B on A.hh=B.hhinsert into B
select *
from
A where not exists(select 1 from b where A.hh=B.hh)
from A
inner join B on A.hh=B.hh
如果数量不为空可以update B set sl=sl + A.sl
from A inner join B on A.hh=B.hh
from A
inner join B on A.hh=B.hh是的。
(
hh varchar(20) not null,
pm varchar(20) null,
sl int null
constraint pk_A_hh primary key
(
hh
)
)create table B
(
hh varchar(20) not null,
pm varchar(20) null,
sl int null,
primary key (hh)
)insert into A
select '10000', 'XXXX', 60 union all
select '10001', 'XXXX', 10 union all
select '10002', 'XXXX', 50 union all
select '10003', 'XXXX', 30 union all
select '10004', 'XXXX', 40 union all
select '10005', 'XXXX', 80 union all
select '10006', 'XXXX', 70insert into B
select '10001', 'XXXX', 90 union all
select '10007', 'XXXX', 30select hh, pm, sum(isnull(sl, 0))
from
(
select hh, pm, sl from A
union all
select hh, pm, sl from B
) agroup by hh, pm
update b
set s1=s1+isnull((select sum(isnull(A.sl,0) from a where a.hh=b.hh),0)
(
hh int ,
pm varchar(50),
sl int
)create table BBB
(
hh int ,
pm varchar(50),
sl int
)insert into aaaselect 10000 ,'XXXX' ,60
union all
select 10001 , 'XXXX' , 10
union all
select 10025 , 'XXXX' , 50
union all
select 35685 , 'XXXX' , 20
union all
select 20523 , 'XXXX' , 20
union all
select 12584 , 'XXXX' , 1000
union all
select 58954 , 'XXXX' , 500
union all
select 52148 , 'XXXX' , 2000insert into bbb
select 10001 , 'XXXX' , null
union all
select 20523 , 'XXXX' , 120
union all
select 12584 , 'XXXX', 2000
union all
select 58954 , 'XXXX', 600
union all
select 52148 ,'XXXX' , 3000
insert into bbb select * from aaa where hh not in (select hh from bbb)update bbb set sl = isnull(bbb.sl,0)+isnull(aaa.sl,0) from aaa inner join bbb on aaa.hh = bbb.hh
select hh,pm ,sum(isnull(sl,0)) as sl
from
(
select * from aaa
union all
select * from bbb
)agroup by hh,pm