with a(djbh,dcdw,drdw,shl,[date])as( select 'DBC001','ck1','ck2',8,'2013-02-01' union all select 'DBC002','ck2','ck3',6,'2013-02-06' union all select 'DBC003','ck3','ck1',3,'2013-02-08') ,b(djbh,dcdw,drdw,shl,[date])as( select 'DBR001','ck1','ck2',2,'2013-02-03' union all select 'DBR002','ck2','ck3',1,'2013-02-09' union all select 'DBR003','ck3','ck1',1,'2013-02-10') ,c as( select dcdw,drdw,shl,date from a where dcdw='ck2' or drdw='ck2' union all select dcdw,drdw,shl,date from b where dcdw='ck2' or drdw='ck2' ) select dcdw,drdw,shl= case when dcdw='ck2' then shl*-1 when drdw='ck2' then shl end, zshl=(select SUM( case when dcdw='ck2' then shl*-1 when drdw='ck2' then shl end) from c where date<=d.date),date from c d order by date
IF ( object_id('Tempdb..#kccx') is not null) DROP TABLE #kccx go create table #kccx (lssh int IDENTITY(1,1) PRIMARY KEY, dcdw varchar(125), drdw varchar(125), shl int, zshl int, [date] datetime) insert into #kccx(dcdw,drdw,shl,[date]) select t.dcdw,t.drdw,t.shl,t.[date] from (select dcdw,drdw,shl,[date] from dbckhz union all select dcdw,drdw,shl,[date] from dbrkhz )t where t.drdw='ck2' or t.dcdw='ck2' order by t.[date] update #kccx set shl=shl*-1 where dcdw='ck2' update A set zshl=(select sum(shl) from #kccx where lssh<=A.lssh) from #kccx as A
with a(djbh,dcdw,drdw,shl,[date])as(
select 'DBC001','ck1','ck2',8,'2013-02-01' union all
select 'DBC002','ck2','ck3',6,'2013-02-06' union all
select 'DBC003','ck3','ck1',3,'2013-02-08')
,b(djbh,dcdw,drdw,shl,[date])as(
select 'DBR001','ck1','ck2',2,'2013-02-03' union all
select 'DBR002','ck2','ck3',1,'2013-02-09' union all
select 'DBR003','ck3','ck1',1,'2013-02-10')
,c as(
select dcdw,drdw,shl,date from a where dcdw='ck2' or drdw='ck2'
union all
select dcdw,drdw,shl,date from b where dcdw='ck2' or drdw='ck2'
)
select dcdw,drdw,shl=
case when dcdw='ck2' then shl*-1
when drdw='ck2' then shl end,
zshl=(select SUM(
case when dcdw='ck2' then shl*-1
when drdw='ck2' then shl end) from c where date<=d.date),date
from c d order by date
DROP TABLE #kccx
go
create table #kccx
(lssh int IDENTITY(1,1) PRIMARY KEY,
dcdw varchar(125),
drdw varchar(125),
shl int,
zshl int,
[date] datetime)
insert into #kccx(dcdw,drdw,shl,[date])
select t.dcdw,t.drdw,t.shl,t.[date] from (select dcdw,drdw,shl,[date] from dbckhz
union all
select dcdw,drdw,shl,[date] from dbrkhz )t
where t.drdw='ck2' or t.dcdw='ck2' order by t.[date]
update #kccx set shl=shl*-1 where dcdw='ck2'
update A set zshl=(select sum(shl) from #kccx where lssh<=A.lssh) from #kccx as A
已经测试