求条SQL语句

[linjie0000]

表1

CBYF ZSF

200811 20

200812

表2

CBYF ZSF

200811 30

200812 40

我想把表1 表2的字段值ZSF 相加
得出来的效果是
CBYF ZSF

200811 50

200812 40

[水族杰纶]

SELECT CBYF  ,  SUM(ZSF)ZSF FROM (
SELECT * FROM 表1
UNION ALL
SELECT * FROM 表2
)T GROUP BY CBYF

[百年树人]

select 
  a.CBYF,
  sum(a.ZSF)+sum(b.ZSF) as ZSF
from 表1 a,表2 b

[百年树人]

---测试数据---
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([CBYF] int,[ZSF] int)
insert [表1]
select 200811,20 union all
select 200812,null
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([CBYF] int,[ZSF] int)
insert [表2]
select 200811,30 union all
select 200812,40
 
---查询---
select 
  a.CBYF,
  sum(isnull(a.ZSF,0))+sum(isnull(b.ZSF,0)) as ZSF
from 表1 a,表2 b
where a.CBYF=b.CBYF
group by a.CBYF

---结果---
CBYF        ZSF         
----------- ----------- 
200811      50
200812      40

（所影响的行数为 2 行）

[lass_name]

up

[水族杰纶]

--借樓上數據
---测试数据---
if object_id('[表1]') is not null drop table [表1]
go
create table [表1]([CBYF] int,[ZSF] int)
insert [表1]
select 200811,20 union all
select 200812,null
if object_id('[表2]') is not null drop table [表2]
go
create table [表2]([CBYF] int,[ZSF] int)
insert [表2]
select 200811,30 union all
select 200812,40
SELECT CBYF  ,  SUM(ZSF)ZSF FROM (
SELECT * FROM 表1
UNION ALL
SELECT * FROM 表2
)T GROUP BY CBYF
/*CBYF        ZSF         
----------- ----------- 
200811      50
200812      40

(影響 2 個資料列)

警告: 彙總 (aggregate) 或其他 SET 操作已消除 Null 值。*/

[ljzs713122]

select
a.CBYF,
sum(isnull(a.ZSF,0))+sum(isnull(b.ZSF,0)) as ZSF
from 表1 a,表2 b
where a.CBYF=b.CBYF
group by a.CBYF

这样可以的呢！

[ljhcy99]

select sum(ZSF) as ZSF, CBYF
from
(

select sum(ZSF) as ZSF, CBYF from 表1
group by CBYF
union all
select sum(ZSF) as ZSF,CBYF from 表2
group by CBYF
) AS A
group by CBYF

[pengxuan]

if object_id('table1') is not null
   drop table table1
go
create table table1(CBYF varchar(6),ZSF varchar(10))
go
insert into table1 values ('200811','20')
insert into table1 (CBYF) values ('200912')
go
if object_id('table2') is not null
   drop table table2
go
create table table2(CBYF varchar(6),ZSF varchar(10))
go
insert into table2 values ('200811','30')
insert into table2 values ('200912','40')
go

select CBYF,sum(cast(ZSF as int)) from (
select CBYF,ZSF=isnull(ZSF,0) from table1
union all
select CBYF,ZSF=isnull(ZSF,0) from table2
)a group by CBYF

[kdymh]

DECLARE @Table1 table (CBYF varchar(20),ZSF varchar(10))
DECLARE @Table2 table (CBYF varchar(20),ZSF varchar(10))

INSERT INTO @Table1 VALUES('200811',20)
INSERT INTO @Table1 VALUES('200812',0)

INSERT INTO @Table2 VALUES('200811',30)

INSERT INTO @Table2 VALUES('200812',40)

SELECT CBYF,sum(ZSF) AS ZSF
FROM(
SELECT CBYF,cast(ZSF as int) as ZSF FROM @Table1
UNION ALL
SELECT CBYF,cast(ZSF as int) as ZSF FROM @Table2
)A
GROUP BY CBYF
ORDER BY CBYF

[lzd1_83]

SELECT CBYF  ,  SUM(ZSF)ZSF FROM (
SELECT * FROM 表1
UNION ALL
SELECT * FROM 表2
)T GROUP BY CBYF

[ChinaITOldMan]

SELECT CBYF , SUM(ZSF)ZSF FROM (
SELECT * FROM 表1
UNION ALL
SELECT * FROM 表2
)T GROUP BY CBYF

[firehand01]

select 表1.CBYF,sum(isnull(表1.ZSF,0))+sum(isnull(表2.ZSF,0))
from 表1 left join 表2 on 表1.CBYF=表2.CBYF
group by 表1.CBYF