表1
id 种类 数量
1 a 78
1 ab 33
1 c 23
2 a 32
2 c 40
3 d 33
4 a 32
5 c 90
5 ab 89
.....
表2
id 种类 数量
1 a 8
1 c 65
1 d 34
2 a 32
3 d 33
5 c 6
....如何将表1中的数据中的需要部分加到表2中
形成
id 种类 数量
1 a 8+78
1 c 65+23
1 d 34
2 a 32+32
3 d 33+33
5 c 6+90
....
id 种类 数量
1 a 78
1 ab 33
1 c 23
2 a 32
2 c 40
3 d 33
4 a 32
5 c 90
5 ab 89
.....
表2
id 种类 数量
1 a 8
1 c 65
1 d 34
2 a 32
3 d 33
5 c 6
....如何将表1中的数据中的需要部分加到表2中
形成
id 种类 数量
1 a 8+78
1 c 65+23
1 d 34
2 a 32+32
3 d 33+33
5 c 6+90
....
(select * from 表1
union all
select * from 表2) T
group by id,种类
insert into AA values(1,'a','78')
insert into AA values(1,'ab','33')
insert into AA values(1,'c','23')
insert into AA values(2,'a','32')
insert into AA values(2,'c','40')
insert into AA values(3,'d','33')
insert into AA values(4,'a','32')
insert into AA values(5,'c','90')
insert into AA values(5,'ab','89')create table BB(id int ,种类 varchar(3) , 数量 varchar(40))
insert into BB values(1,'a','8')
insert into BB values(1,'c','65')
insert into BB values(1,'d','34')
insert into BB values(2,'a','30')
insert into BB values(3,'d','33')
insert into BB values(5,'c','6')
update BB
set 数量=BB.数量+'+'+AA.数量
from AA
where BB.id=AA.id and BB.种类=AA.种类 select * from BB
create table AA(id int ,种类 varchar(3) , 数量 varchar(40))
insert into AA values(1,'a','78')
insert into AA values(1,'ab','33')
insert into AA values(1,'c','23')
insert into AA values(2,'a','32')
insert into AA values(2,'c','40')
insert into AA values(3,'d','33')
insert into AA values(4,'a','32')
insert into AA values(5,'c','90')
insert into AA values(5,'ab','89')create table BB(id int ,种类 varchar(3) , 数量 varchar(40))
insert into BB values(1,'a','8')
insert into BB values(1,'c','65')
insert into BB values(1,'d','34')
insert into BB values(2,'a','32')
insert into BB values(3,'d','33')
insert into BB values(5,'c','6')
select t.id,t.种类,isnull(t.数量,s.数量) 数量
from
(select BB.id,BB.种类, 数量=convert(int,AA.数量)+convert(int,BB.数量)
from AA right join BB on AA.id=BB.id and AA.种类=BB.种类)t
join BB s on t.id=s.id and t.种类=s.种类id 种类 数量
----------- ---- -----------
1 a 86
1 c 88
1 d 34
2 a 64
3 d 66
5 c 96(所影响的行数为 6 行)
create table TEST_TB1(id int,sort varchar(6),qty int)
insert into TEST_TB1
select 1, 'a', 78 union all
select 1, 'ab', 33 union all
select 1, 'c', 23 union all
select 2, 'a', 32 union all
select 2, 'c', 40 union all
select 3, 'd', 33 union all
select 4, 'a', 32 union all
select 5, 'c', 90 union all
select 5, 'ab', 89create table TEST_TB2(id int,sort varchar(6),qty int)
insert into TEST_TB2
select 1, 'a', 8 union all
select 1, 'c', 65 union all
select 1, 'd', 34 union all
select 2, 'a', 32 union all
select 3, 'd', 33 union all
select 5, 'c', 6--测试语句:
select b.id as 'ID',b.sort as 种类,isnull(a.qty,0)+b.qty as 数量 from TEST_TB2 b left join TEST_TB1 a on b.id=a.id and b.sort=a.sort--删除测试表:
drop table TEST_TB1,TEST_TB2--结果:
/*
ID 种类 数量
----------- ------ -----------
1 a 86
1 c 88
1 d 34
2 a 64
3 d 66
5 c 96(所影响的行数为 6 行)
*/
create table AA(id int ,种类 varchar(3) , 数量 varchar(40))
insert into AA values(1,'a','78')
insert into AA values(1,'ab','33')
insert into AA values(1,'c','23')
insert into AA values(2,'a','32')
insert into AA values(2,'c','40')
insert into AA values(3,'d','33')
insert into AA values(4,'a','32')
insert into AA values(5,'c','90')
insert into AA values(5,'ab','89')create table BB(id int ,种类 varchar(3) , 数量 varchar(40))
insert into BB values(1,'a','8')
insert into BB values(1,'c','65')
insert into BB values(1,'d','34')
insert into BB values(2,'a','30')
insert into BB values(3,'d','33')
insert into BB values(5,'c','6')
select aa.id,aa.种类,数量=cast(aa.数量 as int)+cast(bb.数量 as int) from aa join bb on aa.id=bb.id and aa.种类=bb.种类------------
id 种类 数量
----------- ---- -----------
1 a 86
1 c 88
2 a 62
3 d 66
5 c 96(所影响的行数为 5 行)
没看清楚
select bb.id,bb.种类,数量=isnull(cast(aa.数量 as int),0)+cast(bb.数量 as int) from aa right join bb on aa.种类=bb.种类 and aa.id=bb.id----------- ---- -----------
1 a 86
1 c 88
1 d NULL
2 a 62
3 d 66
5 c 96(所影响的行数为 6 行)
(
id int,
kind varchar(5),
Qty int
)create table Tb2
(
id int,
kind varchar(5),
Qty int
)insert into Tb1
select 1 as id, 'a' as kind, 78 as Qty
union select 1 , 'ab' , 33
union select 1 , 'c' , 23
union select 2 , 'a' , 32
union select 2 , 'c' , 40
union select 3 , 'd' , 33
union select 4 , 'a' , 32
union select 5 , 'c' , 90
union select 5 , 'ab' , 89
insert into Tb2
select 1 as id, 'a' as kind, 8 as Qty
union select 1 , 'c' , 65
union select 1 , 'd' , 34
union select 2 , 'a' , 32
union select 3 , 'd' , 33
union select 5 , 'c' , 6select * from Tb1
select * from Tb2-----------------------------------------------------------------------------
--处理语句:
update Tb2
set Qty=Qty+isnull((select Qty from Tb1
where Tb1.id=Tb2.id and Tb1.kind=Tb2.kind
),0)
from Tb2
-------------------------------------------------------------------------------
select * from Tb2测试通过。id kind Qty
----------- ----- -----------
1 a 78
1 ab 33
1 c 23
2 a 32
2 c 40
3 d 33
4 a 32
5 ab 89
5 c 90(9 件処理されました)id kind Qty
----------- ----- -----------
1 a 8
1 c 65
1 d 34
2 a 32
3 d 33
5 c 6(6 件処理されました)
(6 件処理されました)id kind Qty
----------- ----- -----------
1 a 86
1 c 88
1 d 34
2 a 64
3 d 66
5 c 96(6 件処理されました)
我哪里写错了?
其他的更新和leftjoin都可以考虑,学习了不少。