select [Type]=isnull(a.Type,b.Type),a.total_A_Num,b.total_B_Num from (select Type,sum(num)total_A_Num from a group by type)a full join (select Type,sum(num)total_B_Num from b group by type)b on a.type=b.type
SELECT TYPE=CASE WHERN ISNULL(A.TYPE,'')<>'' THEN A.TYPE ELSE B.TYPE END, SUM(A.NUM) AS ANUM ,SUM(B.NUM) AS BSUM FROM TA A FULL JOIN TB B ON A.TYPE = B.TYPE GROUP BY A.TYPE
declare @ta table ([type] int,num int) insert into @ta select 1,10 insert into @ta select 2,30 insert into @ta select 1,15 insert into @ta select 3,30 insert into @ta select 3,20declare @tb table ([type] int,num int) insert into @tb select 1,50 insert into @tb select 2,15 insert into @tb select 1,20 insert into @tb select 3,30 insert into @tb select 2,20 select [type], sum(case when tbid=1 then num else 0 end) as total_A_Num, sum(case when tbid=2 then num else 0 end) as total_b_Num from ( select *,tbid=1 from @ta union all select *,tbid=2 from @tb )t group by [type]type total_A_Num total_b_Num 1 25 70 2 30 35 3 50 30
select t1.type,t1.numa ,t2.numB from (select type ,sum(num ) as numa from A group by type )t1 ,(select type ,sum(num ) as numB from A group by type ) t2 where t1.type=t2.type
select isnull(t1.type,t2.type) type,t1.numa ,t2.numB from (select type ,sum(num ) as numa from A group by type )t1 full join (select type ,sum(num ) as numB from A group by type ) t2 on t1.type=t2.type
select isnull(t1.type,t2.type) type,t1.numa ,t2.numB from (select type ,sum(num ) as numa from A group by type )t1 full join (select type ,sum(num ) as numB from A group by type ) t2 on t1.type=t2.type
drop table at create table at(type int,num int) insert into at select 1,10 union all select 2,30 union all select 1,15 union all select 3,30 union all select 3,20 drop table bt create table bt(type int,num int)insert into bt select 1,50 insert into bt select 2,15 insert into bt select 1,20 insert into bt select 3,30 insert into bt select 2,20 go select a.type,a.num,b.num from (select type,sum(num) as num from at group by type) a full join (select type,sum(num) as num from bt group by type) b on a.type=b.type
[Type]=isnull(a.Type,b.Type),a.total_A_Num,b.total_B_Num
from
(select Type,sum(num)total_A_Num from a group by type)a
full join
(select Type,sum(num)total_B_Num from b group by type)b on a.type=b.type
ELSE B.TYPE
END,
SUM(A.NUM) AS ANUM ,SUM(B.NUM) AS BSUM
FROM TA A
FULL JOIN TB B
ON A.TYPE = B.TYPE
GROUP BY A.TYPE
insert into @ta select 1,10
insert into @ta select 2,30
insert into @ta select 1,15
insert into @ta select 3,30
insert into @ta select 3,20declare @tb table ([type] int,num int)
insert into @tb select 1,50
insert into @tb select 2,15
insert into @tb select 1,20
insert into @tb select 3,30
insert into @tb select 2,20
select [type],
sum(case when tbid=1 then num else 0 end) as total_A_Num,
sum(case when tbid=2 then num else 0 end) as total_b_Num
from (
select *,tbid=1 from @ta
union all
select *,tbid=2 from @tb
)t
group by [type]type total_A_Num total_b_Num
1 25 70
2 30 35
3 50 30
select t1.type,t1.numa ,t2.numB
from (select type ,sum(num ) as numa from A group by type )t1 ,(select type ,sum(num ) as numB from A group by type ) t2
where t1.type=t2.type
select isnull(t1.type,t2.type) type,t1.numa ,t2.numB
from (select type ,sum(num ) as numa from A group by type )t1
full join
(select type ,sum(num ) as numB from A group by type ) t2
on t1.type=t2.type
select isnull(t1.type,t2.type) type,t1.numa ,t2.numB
from (select type ,sum(num ) as numa from A group by type )t1
full join
(select type ,sum(num ) as numB from A group by type ) t2
on t1.type=t2.type
create table at(type int,num int)
insert into at
select 1,10 union all
select 2,30 union all
select 1,15 union all
select 3,30 union all
select 3,20
drop table bt
create table bt(type int,num int)insert into bt select 1,50
insert into bt select 2,15
insert into bt select 1,20
insert into bt select 3,30
insert into bt select 2,20
go
select a.type,a.num,b.num
from
(select type,sum(num) as num from at group by type) a
full join
(select type,sum(num) as num from bt group by type) b
on a.type=b.type