select * into #tmp
from TdailyJobcreate table #Result(
BatchID int,OutJobID int,InJobID int,Number int)while exists (select * from #tmp a where JobType='in' and Number>0 and exists ((select * from #tmp where BatchID=a.BatchID and JobType='out' and Number>0)) )
begininsert #result
select a.BatchID,a.dJID,b.DJID,case when a.Number>b.Number then b.Number else a.Number end
from (
select * from #tmp a
where JobType='in' and number>0
and not exists (select * from #tmp where BatchID=a.BatchID and JobType='in' and number>0 and djid<a.djid)
) as a,(
select * into #tmpout from #tmp a
where JobType='out' and number>0
and not exists (select * from #tmp where BatchID=a.BatchID and JobType='out' and number>0 and djid<a.djid)
) as b
where a.BatchID=b.BatchIDupdate c
set number=c.number-case when a.Number>b.Number then b.Number else a.Number end
from (
select * from #tmp a
where JobType='in' and number>0
and not exists (select * from #tmp where BatchID=a.BatchID and JobType='in' and number>0 and djid<a.djid)
) as a,(
select * into #tmpout from #tmp a
where JobType='out' and number>0
and not exists (select * from #tmp where BatchID=a.BatchID and JobType='out' and number>0 and djid<a.djid)
) as b,#Tmp c
where a.BatchID=b.BatchID
and (a.djid=c.djid or b.djid=c.djid)endselect * from #result
drop table #tmp,#result
哈,哈哈,哈哈哈......
from TdailyJobcreate table #Result(
BatchID int,OutJobID int,InJobID int,Number int)while exists (select * from #tmp a where JobType='in' and Number>0 and exists ((select * from #tmp where BatchID=a.BatchID and JobType='out' and Number>0)) )
begininsert #result
select a.BatchID,a.dJID,b.DJID,case when a.Number>b.Number then b.Number else a.Number end
from (
select * from #tmp a
where JobType='in' and number>0
and not exists (select * from #tmp where BatchID=a.BatchID and JobType='in' and number>0 and djid<a.djid)
) as a,(
select * into #tmpout from #tmp a
where JobType='out' and number>0
and not exists (select * from #tmp where BatchID=a.BatchID and JobType='out' and number>0 and djid<a.djid)
) as b
where a.BatchID=b.BatchIDupdate c
set number=c.number-case when a.Number>b.Number then b.Number else a.Number end
from (
select * from #tmp a
where JobType='in' and number>0
and not exists (select * from #tmp where BatchID=a.BatchID and JobType='in' and number>0 and djid<a.djid)
) as a,(
select * into #tmpout from #tmp a
where JobType='out' and number>0
and not exists (select * from #tmp where BatchID=a.BatchID and JobType='out' and number>0 and djid<a.djid)
) as b,#Tmp c
where a.BatchID=b.BatchID
and (a.djid=c.djid or b.djid=c.djid)endselect * from #result
drop table #tmp,#result
哈,哈哈,哈哈哈......
解决方案 »
- "启动服务帐户"的设置有何作用呀,系统帐户指什么,与本帐户有何区别,一般应该如何设置呀?我不知这个设置对什么会产生影响
- 定下varchar 16位,不足位时在右边补上0,求update语句
- 大家好。这个问题有点难!
- 使用 UPDATETEXT 情况
- 求相似关键词检索的方案,急,5月期间在线等
- 存储过程可以返回记录集吗?
- 源碼出售, vb + sql server(有意者請進)或者尋求合作
- 大家来讨论看,做一个全省联网的程序(VB+SQL Server)采用哪种解决方案更有效、更经济
- 怎样将ActiveX控件连同VFP主程序一同发布?
- 多表更新的一个问题
- 急!急!急!如何一个int型字段中插入一个和自动增长的标识字段的一样的值?
- 关系SQL Server 2000的怪问题
的吧.更简单的办法还没想到.