select 提交资料编号 as 编号,sum(正本) as 正本,sum(副本) as 副本,sum(复印件) as 复印件
from ( select * from 提交资料数量
union all
select 0,-送审编号,-正本,-副本,-复印件 from 送审资料数量
) as a
group by 提交资料编号
from ( select * from 提交资料数量
union all
select 0,-送审编号,-正本,-副本,-复印件 from 送审资料数量
) as a
group by 提交资料编号
(Select 提交编号,Sum(IsNull(正本,0)) As 正本,Sum(IsNull(副本,0)) As 副本,Sum(IsNull(复印件,0)) As 复印件
From
Group By [提交资料数量]) As a
(Select 送审编号,Sum(IsNull(正本,0)) As 正本,Sum(IsNull(副本,0)) As 副本,Sum(IsNull(复印件,0)) As 复印件
From
Group By [送审资料数量]) As b
Where a.提交编号=b.送审编号
Select [提交编号]、sum(正本),sum(副本),sum(复印件) From [提交资料数量] Group by [提交编号] Into #T
二、两个表相减
Select T1.正本 - T2.正本 ,T1.复印件 - T2.复印件,T1.提交资料数量 - T2.提交资料数量
From [送审资料数量] as T2, #t as T1
Where T1.提交编号=T2.提交编号
select 提交编号,正本=sum(正本),副本=sum(副本),复印件=sum(复印件) from 提交资料数量 Group by 提交编号) a
left join (select 送审编号,正本=sum(正本),副本=sum(副本),复印件=sum(复印件) from 送审资料数量 Group by 送审编号) b
on a.提交编号=b.送审编号)
select 提交编号 as 编号,-sum(正本) as 正本,-sum(副本) as 副本,-sum(复印件) as 复印件
from 提交资料
group by 提交编号
union all
select 送审编号,sum(正本) as 正本,sum(副本) as 副本,sum(复印件) as 复印件
from 送审资料
) a
group by 编号
selet 编号,正本,副本,复印件 from (
select 提交编号 as 编号,-sum(正本) as 正本,-sum(副本) as 副本,-sum(复印件) as 复印件
from 提交资料
group by 提交编号
union all
select 送审编号,sum(正本) as 正本,sum(副本) as 副本,sum(复印件) as 复印件
from 送审资料 group by 送审编号
) a
group by 编号
insert into sendcheck select 1,2,3,4
create table refercheck(id1 int,a1 int,b1 int,c1 int)
insert into refercheck select 1,3,4,5
union all select 1,3,4,5
union all select 2,9,9,9select id,sum(a) as a,sum(b) as b,sum(c) as c
from(
select id,0-a as a,0-b as b,0-c as c from sendcheck
union all
select id1,a1,b1,c1 from refercheck)t
group by id
drop table sendcheck
drop table refercheck
Create Table 送审资料数量(编号 int,送审编号 int, 正本 int,副本 int,复印件 int)
Create Table 提交资料数量(编号 int,提交编号 int, 正本 int,副本 int,复印件 int)
insert into 送审资料数量
select 10,55,2,3,4
insert into 提交资料数量
select 27,55,3,4,5 union
select 28,55,3,4,5 union
select 29,58,9,9,9
--------------------------下面實現語句----------------------------------------------------
select 编号=提交编号,正本=abs(isnull(a.正本,0)-isnull(b.正本,0)),副本=abs(isnull(a.副本,0)-isnull(b.副本,0)),复印件=abs(isnull(a.复印件,0)-isnull(b.复印件,0)) from (
select 提交编号,正本=sum(正本),副本=sum(副本),复印件=sum(复印件) from 提交资料数量 Group by 提交编号) a
left join (select 送审编号,正本=sum(正本),副本=sum(副本),复印件=sum(复印件) from 送审资料数量 Group by 送审编号) b
on (a.提交编号=b.送审编号)
-----------------------結果------------------------------------
编号 正本 副本 复印件
55 4 5 6
58 9 9 9