--> By dobear_0922(小熊) 2009-01-15 16:44:00 --> 测试数据:[A] if object_id('[A]') is not null drop table [A] create table [A]([ID] int,[SumClaim] int,[SumDefLoss] int,[Sumpaid] int,[style] int) insert [A] select 1,1000,1000,1500,1 union all select 2,1000,1500,null,2 union all select 3,500,null,null,2 union all select 4,300,500,null,2 --> 测试数据:[B] if object_id('[B]') is not null drop table [B] create table [B]([ID] int,[ComSumPaid] int,[style] int) insert [B] select 6,1000,1 union all select 7,2000,2select [style], sum([ComSumPaid]) as [ComSumPaid] from ( select ID, [ComSumPaid]=coalesce([Sumpaid],[SumDefLoss],[SumClaim]),[style] from A union all select * from B ) T group by [style]/* style ComSumPaid ----------- ----------- 1 2500 2 4500(2 行受影响) */drop table [A], [B]
--> 测试数据:[A]
if object_id('[A]') is not null drop table [A]
create table [A]([ID] int,[SumClaim] int,[SumDefLoss] int,[Sumpaid] int,[style] int)
insert [A]
select 1,1000,1000,1500,1 union all
select 2,1000,1500,null,2 union all
select 3,500,null,null,2 union all
select 4,300,500,null,2
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
create table [B]([ID] int,[ComSumPaid] int,[style] int)
insert [B]
select 6,1000,1 union all
select 7,2000,2select [style], sum([ComSumPaid]) as [ComSumPaid]
from
(
select ID, [ComSumPaid]=coalesce([Sumpaid],[SumDefLoss],[SumClaim]),[style] from A
union all
select * from B
) T
group by [style]/*
style ComSumPaid
----------- -----------
1 2500
2 4500(2 行受影响)
*/drop table [A], [B]