在SqlServer 2008中计算两个字段之间的运行总数

Question

想象下表：

prprno      prprdt    pritcd     prqnty    popono      poqnty
----------  --------  --------   --------  --------    --------
2013100017  28-10-13  220010284  2000      2013100017  800
2013100017  28-10-13  220010284  2000      2013100018  500
2013100017  28-10-13  220010284  2000      2013100019  500
2013100017  28-10-13  220010284  2000      2013100020  200

我希望查询返回一个正在运行的总计(Poqnty)。

prprno      prprdt    pritcd     prqnty    popono      poqnty    blnce
----------  --------  --------   --------  --------    --------  ---------- 
2013100017  28-10-13  220010284  2000      2013100017  800       1200
2013100017  28-10-13  220010284  2000      2013100018  500        700
2013100017  28-10-13  220010284  2000      2013100019  500        200
2013100017  28-10-13  220010284  2000      2013100020  200          0

有一个采购申请(2013100017)与一个项目(220010284) &该项目收到与不同的采购订单编号。(popono)和(poqnty)。我要平衡一下这个项目。

从pr.prcocd，pr.prprno，prprdt，pr.pritcd，pr.pritcc，iname，iunit，prsrno，prqnty到#tmppr从fisprq10 pr内部连接到pr.prcocd=it.icocd，pr.pritcd=it.icode和pr.pritcc=it.icccd，其中pr.prcod=‘001’和pr.prprno在2013100017到2013100017之间，pr.prprdt在'2013-10-01‘和'2013-10-31’之间，pr.prprdt，pr.prprno，pr.prsrno

select po.pococd, po.popono, po.popodt, po.poprty, po.poptcc, cu.mcdesc, po.poqnty, po.poprno, po.poitcd, po.poitcc
into #tmppo
from fispod10 po
inner join fglcust cu on po.pococd=cu.mccocd and po.poprty=cu.mccode and po.poptcc=cu.mccccd 
where po.pococd='001' and cu.mccs='S' and po.poopbl<>'Y'
and po.poprno between 2013100017 and 2013100017
and po.popodt <= '2013-10-31'
order by po.poprno

select pr.prprno, max(pr.prprdt) as prprdt, pr.pritcd, pr.pritcc, max(pr.iname) as iname, max(pr.iunit) as iunit,
sum(pr.prqnty) as prqnty,
isnull(po.popono, 0) as popono, max(isnull(po.poprty, '')) as poprty, max(isnull(po.poptcc, '')) as poptcc,
max(isnull(po.mcdesc, '')) as mcdesc, sum(isnull(po.poqnty, 0)) as poqnty
from #tmppr pr
left outer join #tmppo po on pr.prprno=po.poprno and pr.pritcd=po.poitcd and pr.pritcc=po.poitcc
group by pr.prprno, pr.pritcd, pr.pritcc, po.popono
order by 1, 2, 3;

Answer 1

请尝试：

;with T as(
    select *, 
        ROW_NUMBER() over (order by prprno) RNum
    From YourTable
)
select 
    prprno,   
    prprdt, 
    pritcd, 
    prqnty, 
    popono, 
    poqnty,
    prqnty-(select SUM(poqnty) from T b where b.RNum<=a.RNum) blnce
from T a

Sql Fiddle演示

对于不同的pritcd，请查看以下查询。

;with T as(
    select *, 
        ROW_NUMBER() over (order by prprno) RNum
    From YourTable
)
select 
    prprno,   
    prprdt, 
    pritcd, 
    prqnty, 
    popono, 
    poqnty,
    prqnty-(select SUM(poqnty) from T b where b.RNum<=a.RNum and b.pritcd=a.pritcd) blnce
from T a

Answer 2

您可以将它写成一个关联子查询：

SELECT prprno ,prprdt ,pritcd ,prqnty , popono ,poqnty ,
    (SELECT SUM(T2.poqnty)
    FROM table1 AS T2
    WHERE T2.prprno = T1.prprno  --one purchase requisition 
    AND T2.pritcd = T1.pritcd
    AND T2.popono > t1.popono) AS blnce
FROM table1 AS T1
ORDER BY T1.popono ;

Answer 3

尝尝这个,

 Declare @tbl table(prprno varchar(50),prprdt varchar(50),pritcd int,prqnty int,popono int,poqnty int)
insert into @tbl
select 2013100017,  '28-10-13',  220010284,  2000,      2013100017,  800
union all
select 2013100017,  '28-10-13',  220010284,  2000,      2013100018,  500
union all
select 2013100017,  '28-10-13',  220010284,  2000,      2013100019,  500
union all
select 2013100017,  '28-10-13',  220010284,  2000,      2013100020,  200

select ta.prprno,
      ta.prprdt,
      ta.pritcd,
      ta.prqnty,
      ta.popono,
      ta.poqnty, 
      isnull((select sum(poqnty) from @tbl where ta.popono < popono),0) as blnce
from @tbl ta
inner join @tbl tb on ta.prprno=tb.prprno and ta.popono=tb.popono