我是被楼主盯上了吗,不知道记录多了会不会执行的时候慢,下面就是结果了: declare @t table ( recid int, ioorout int,spid int,dj int,sl int ) insert into @t values (1,1,999,590,120),(2,-1,999,800,110),(3,1,999,580,80) ,(4,-1,999,800,50),(5,1,999,570,100),(6,1,999,560,50),(7,-1,999,800,70),(8,-1,999,790,90) ;with tb_total as ( select recid,dj,ioorout,spid, (select SUM(sl) from @t sub where sub.spid=t1.spid and sub.ioorout=t1.ioorout and sub.recid<=t1.recid)-sl from_num, (select SUM(sl) from @t sub where sub.spid=t1.spid and sub.ioorout=t1.ioorout and sub.recid<=t1.recid) to_num from @t t1 ) select t1.recid,t1.spid, SUM(t2.dj * ( case when t1.to_num<t2.to_num then t1.to_num else t2.to_num end - case when t1.from_num>t2.from_num then t1.from_num else t2.from_num end )) Cost from tb_total t1 join tb_total t2 on t1.ioorout=-1 and t2.ioorout=1 and t1.spid=t2.spid and t1.from_num<t2.to_num and t1.to_num>t2.from_num where t1.recid between 4 and 7 group by t1.recid,t1.spid recid spid Cost ----------- ----------- ----------- 4 999 29100 7 999 40300
declare @t table ( recid int, ioorout int,spid int,dj int,sl int )
insert into @t values (1,1,999,590,120),(2,-1,999,800,110),(3,1,999,580,80)
,(4,-1,999,800,50),(5,1,999,570,100),(6,1,999,560,50),(7,-1,999,800,70),(8,-1,999,790,90)
;with tb_total as
(
select recid,dj,ioorout,spid,
(select SUM(sl) from @t sub where sub.spid=t1.spid and sub.ioorout=t1.ioorout and sub.recid<=t1.recid)-sl from_num,
(select SUM(sl) from @t sub where sub.spid=t1.spid and sub.ioorout=t1.ioorout and sub.recid<=t1.recid) to_num
from @t t1
)
select t1.recid,t1.spid,
SUM(t2.dj * (
case when t1.to_num<t2.to_num then t1.to_num else t2.to_num end -
case when t1.from_num>t2.from_num then t1.from_num else t2.from_num end
)) Cost
from tb_total t1
join tb_total t2 on t1.ioorout=-1 and t2.ioorout=1 and t1.spid=t2.spid and t1.from_num<t2.to_num and t1.to_num>t2.from_num
where t1.recid between 4 and 7
group by t1.recid,t1.spid
recid spid Cost
----------- ----------- -----------
4 999 29100
7 999 40300