求个语句，谢谢大家，有分
调拨出库表 dbckhz
表结构都如下
djbh dcdw drdw shl date
DBC001 ck1 ck2 8 2013-02-01
DBC002 ck2 ck3 6 2013-02-06
DBC003 ck3 ck1 3 2013-02-08
调拨入库表 dbrkhz
djbh dcdw drdw shl date
DBR001 ck1 ck2 2 2013-02-03
DBR002 ck2 ck3 1 2013-02-09
DBR003 ck3 ck1 1 2013-02-10
查询结果要求 lssh按时间递增 查询ck2 进出库数量，和库存数量
lssh dcdw drdw shl zshl date
101 ck1 ck2 8 8 2013-02-01
102 ck1 ck2 2 10 2013-02-03
103 ck2 ck3 -6 4 2013-02-06
104 ck2 ck3 -1 3 2013-02-09
求语句 谢谢大家了
两张表确实是没有必要在两张表里，但软件确实操作界面不一样，数据所存的表就是两个。
数值累加?求语句?
------解决方案--------------------
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
------解决方案--------------------
SQL 2005以上版本
declare @str varchar(40)
set @str='ck2'
;with test as
(select dcdw,drdw,shi=case when dcdw=@str then -shi when drdw=@str then shi else 0 end,DATE from dbckhz
union all
select dcdw,drdw,shi=case when dcdw=@str then -shi when drdw=@str then shi else 0 end,DATE from dbrkhz),
cte as
(select rowno=100+row_number() over (order by date),* from test where (dcdw=@str or drdw=@str))
select rowno,dcdw,drdw,shi,zshl=(select SUM(shi) from cte a where a.rowno<=b.rowno),date from cte b