sql语句 按wpbh求和的问题

[liangjianshi]

def:
wpbh kcsl bhsjer hsjer
1 1 1 2
2 10 11 15
3 5 6 8

abc:
wpbh zshliang zbhsjer zhsjer
1 5 2 5
2 -5 -10 -11
3 -2 -5 -6
4 1 2 3
要通过上面两个表，查询得到如下：
aaa:
wpbh shu bhs hs
1 6 3 7
2 5 1 4
3 3 1 2
4 1 2 3
sh字段的值是kcsl+zshliang
bhs字段的值是bhsjer+zbhsjer
hs字段的值是hsjer+zhsjer
请问大家，这个sql语句怎么写呢 (abc表中wpbh为4的纪录，在def表中不存在，那么就该记录直接放到表aaa中)

[xeqtr1982]

declare @t table(wpbh int,kcsl int,bhsjer int,hsjer int)
insert into @t select 1,1,1,2
union all select 2,10,11,15
union all select 3,5,6,8

declare @a table(wpbh int,zshliang int,zbhsjer int,zhsjer int)
insert into @a select 1,5,2,5
union all select 2,-5,-10,-11
union all select 3,-2, -5,-6
union all select 4,1,2,3

select b.wpbh,
shu=isnull(kcsl+zshliang,zshliang),
bhs=isnull(bhsjer+zbhsjer,zbhsjer),
hs=isnull(hsjer+zhsjer,zhsjer)
from @t a,@a b
where a.wpbh=*b.wpbh

[liangjianshi]

不行啊
必须声明变量 '@t'

[liangjianshi]

补充以下：
表adc中的wpbh在def表中可能不存在，表def中的wpbh在表abc中也可能不存在。
但是在查询得到的表aaa中，必须包含所有的

[xeqtr1982]

select b.wpbh,
shu=isnull(kcsl+zshliang,zshliang),
bhs=isnull(bhsjer+zbhsjer,zbhsjer),
hs=isnull(hsjer+zhsjer,zhsjer)
from def a,abc b
where a.wpbh=*b.wpbh

--这样呢

[liangjianshi]

怎么提示 列名 'hsjer' 无效。

[xeqtr1982]

检查你的表名写的顺序是否正确，看看表里是否有hsjer字段

[$扫地僧$]

create table t(wpbh int,kcsl int,bhsjer int,hsjer int)
insert into t select 1,1,1,2
union all select 2,10,11,15
union all select 3,5,6,8

create table a(wpbh int,zshliang int,zbhsjer int,zhsjer int)
insert into a select 1,5,2,5
union all select 2,-5,-10,-11
union all select 3,-2, -5,-6
union all select 4,1,2,3


select wpbh,
sum(kcsl) as kcsl,
sum(bhsjer) as bhsjer,
sum(hsjer) as hsjer
from
(select * from t union all select * from a) T
group by wpbh

[十一月猪]

declare @t table(wpbh int , kcsl int ,bhsjer int ,hsjer int )
declare @t1 table(wpbh int , zshliang int ,zbhsjer int ,zhsjer int )

insert into @t
select 1 , 1 , 1 , 2 union
select 2 , 10 , 11 , 15 union
select 3 , 5 , 6 , 8

insert into @t1
select 1 , 5 , 2 , 5 union
select 2 , -5 , -10 , -11 union
select 3 , -2 , -5 , -6 union
select 4 , 1 , 2 , 3

select b.wpbh ,
isnull(kcsl + zshliang,0) as shu,
isnull(bhsjer + zbhsjer,0) as bhs,
isnull(hsjer + zhsjer ,0) as hs
from @t a
right outer join @t1 b
on a.wpbh = b.wpbh

wpbh shu bhs hs
----------- ----------- ----------- -----------
1 6 3 7
2 5 1 4
3 3 1 2
4 0 0 0

（所影响的行数为 4 行）

[十一月猪]

修改一下
declare @t table(wpbh int , kcsl int ,bhsjer int ,hsjer int )
declare @t1 table(wpbh int , zshliang int ,zbhsjer int ,zhsjer int )

insert into @t
select 1 , 1 , 1 , 2 union
select 2 , 10 , 11 , 15 union
select 3 , 5 , 6 , 8

insert into @t1
select 1 , 5 , 2 , 5 union
select 2 , -5 , -10 , -11 union
select 3 , -2 , -5 , -6 union
select 4 , 1 , 2 , 3

select b.wpbh ,
isnull(kcsl + zshliang,zshliang) as shu,
isnull(bhsjer + zbhsjer,zbhsjer) as bhs,
isnull(hsjer + zhsjer ,zhsjer) as hs
from @t a
right outer join @t1 b
on a.wpbh = b.wpbh
union
select wpbh ,
akcsl as shu,
bhsjer as bhs,
hsjer as hs
from @t
where wpbh
not in
(
select wpbh
from @t1
)

[hyrongg]

create table tbl1(wpbh int, kcsl int, bhsjer int, hsjer int)
insert into tbl1 select 1,1,1,2
union all select 2,10,11,15
union all select 3,5,6,8
create table tbl2(wpbh int,zshliang int,zbhsjer int,zhsjer int)
insert into tbl2 select 1,5,2,5
union all select 2,-5,-10,-11
union all select 3,-2,-5,-6
union all select 4,1,2,3



select coalesce(a.wpbh,b.wpbh) as wpbh,
sum(coalesce(a.kcsl,0))+sum(coalesce(b.zshliang,0)) as shu,
sum(coalesce(a.bhsjer,0))+sum(coalesce(b.zbhsjer,0)) as bhs,
sum(coalesce(a.hsjer,0))+sum(coalesce(b.zhsjer,0)) as hs
from tbl1 a
full outer join tbl2 b on a.wpbh=b.wpbh
group by coalesce(a.wpbh,b.wpbh)

drop table tbl1
drop table tbl2
-------------------------------
1 6 3 7
2 5 1 4
3 3 1 2
4 1 2 3