日期:2014-05-18 浏览次数:20477 次
if object_id('[A]') is not null drop table [A] go create table [A]([clientID] varchar(1),[Indate] datetime,[Inresume] int) insert [A] select 'a','2008-10-10',2000 union all select 'a','2018-10-10',2010 union all select 'b','2028-10-10',2020 union all select 'b','2038-10-10',2030 union all select 'c','2048-10-10',2040 go if object_id('[B]') is not null drop table [B] go create table [B]([clientID] varchar(1),[Indate] datetime,[Outresume] int) insert [B] select 'a','2008-12-10',1000 union all select 'a','2018-12-10',1010 union all select 'b','2028-12-10',1020 union all select 'b','2038-12-10',1030 union all select 'c','2048-12-10',1040 go select clientid,sum(Inresume) as Leftresume from ( select clientid,Inresume from a union all select clientid,-Outresume from b ) t group by clientid /** clientid Leftresume -------- ----------- a 2000 b 2000 c 1000 (3 行受影响) **/
------解决方案--------------------
--> 测试数据:[A1] if object_id('[A1]') is not null drop table [A1] create table [A1]([clientID] varchar(1),[Indate] datetime,[Inresume] int) insert [A1] select 'a','2008-10-10',2000 union all select 'a','2018-10-10',2010 union all select 'b','2028-10-10',2020 union all select 'b','2038-10-10',2030 union all select 'c','2048-10-10',2040 --> 测试数据:[B2] if object_id('[B2]') is not null drop table [B2] create table [B2]([clientID] varchar(1),[Indate] datetime,[Outresume] int) insert [B2] select 'a','2008-12-10',1000 union all select 'a','2018-12-10',1010 union all select 'b','2028-12-10',1020 union all select 'b','2038-12-10',1030 union all select 'c','2048-12-10',1040 ;with t as( select [clientID],[Inresume] from [A1] union all select [clientID],-[Outresume] from [B2] ) select [clientID],SUM([Inresume]) as leftmoney from t group by[clientID] /* clientID leftmoney a 2000 b 2000 c 1000 */