declare @ta table(className varchar(10),o_money int) declare @tb table(className varchar(10),p_money int) insert @ta select 'name1', 312 union all select 'name2', 135 union all select 'name3', 124 union all select 'name1', 514 union all select 'name1', 121 union all select 'name3', 522 union all select 'name5', 512 insert @tb select 'name1', 312 union all select 'name2', 135 union all select 'name3', 124 union all select 'name1', 514 union all select 'name1', 120 union all select 'name8', 125 union all select 'name3', 520select isnull(a.className,b.className) as className, isnull(a.Sum_o_Money,0) as Sum_o_Money, isnull(b.Sum_p_Money,0) as Sum_p_Money, isnull(a.Sum_o_Money,0) - isnull(b.Sum_p_Money,0) as sum_o_moneysum_p_Money from ( select className ,sum(o_money) as Sum_o_Money from @ta group by className ) a full join ( select className ,sum(p_money) as Sum_p_Money from @tb group by className ) b on a.className = b.className
declare @a table(className varchar(10), o_money int) insert @a select 'name1', 312 union all select 'name2', 135 union all select 'name3', 124 union all select 'name1', 514 union all select 'name1', 121 union all select 'name3', 522 union all select 'name5', 512 declare @b table( className varchar(10), p_money int) insert @b select 'name1', 312 union all select 'name2', 135 union all select 'name3', 124 union all select 'name1', 514 union all select 'name1', 120 union all select 'name8', 125 union all select 'name3', 520select classname,Sum_o_money,sum_p_money,(Sum_o_money-sum_p_money) s_money from ( select case when a.classname is null then b.classname else a.classname end classname, case when a.o_money is null then p_money else a.o_money end Sum_o_money, case when b.p_money is null then 0 else b.p_money end sum_p_money from (select classname,sum(o_money) o_money from @a group by classname) a full join (select classname,sum(p_money) p_money from @b group by classname) b on a.classname=b.classname ) c
declare @tb table(className varchar(10),p_money int)
insert @ta
select 'name1', 312 union all
select 'name2', 135 union all
select 'name3', 124 union all
select 'name1', 514 union all
select 'name1', 121 union all
select 'name3', 522 union all
select 'name5', 512
insert @tb
select 'name1', 312 union all
select 'name2', 135 union all
select 'name3', 124 union all
select 'name1', 514 union all
select 'name1', 120 union all
select 'name8', 125 union all
select 'name3', 520select
isnull(a.className,b.className) as className,
isnull(a.Sum_o_Money,0) as Sum_o_Money,
isnull(b.Sum_p_Money,0) as Sum_p_Money,
isnull(a.Sum_o_Money,0) - isnull(b.Sum_p_Money,0) as sum_o_moneysum_p_Money
from
(
select className ,sum(o_money) as Sum_o_Money from @ta group by className
) a
full join
(
select className ,sum(p_money) as Sum_p_Money from @tb group by className
) b
on a.className = b.className
insert @a select 'name1', 312
union all select 'name2', 135
union all select 'name3', 124
union all select 'name1', 514
union all select 'name1', 121
union all select 'name3', 522
union all select 'name5', 512
declare @b table( className varchar(10), p_money int)
insert @b select 'name1', 312
union all select 'name2', 135
union all select 'name3', 124
union all select 'name1', 514
union all select 'name1', 120
union all select 'name8', 125
union all select 'name3', 520select classname,Sum_o_money,sum_p_money,(Sum_o_money-sum_p_money) s_money from
(
select
case when a.classname is null then b.classname else a.classname end classname,
case when a.o_money is null then p_money else a.o_money end Sum_o_money,
case when b.p_money is null then 0 else b.p_money end sum_p_money
from
(select classname,sum(o_money) o_money from @a group by classname) a
full join
(select classname,sum(p_money) p_money from @b group by classname) b
on a.classname=b.classname
) c
name1……name……n :)
谢谢~:)