重算最后一行

Question

如下数据，想要重算最后一行QTY的值。
1、QTY=INQTY+OUTQTY
2、当QTY>0时 QTY=INQTY
3、一句话能否得到如下效果
goods_no              indate                   inqty                    outqty           qty
 ---------------- ----------------------- ---------------- ------------------------ -----------------
11012030-1010          2009-06-17 5000.0000            -2300.0000         0.0000
11012030-1010          2009-06-17 100.0000             -2300.0000         0.0000
11012030-1010          2009-06-18 200.0000             -2300.0000         0.0000
11012030-1010          2009-06-18 100.0000             -2300.0000         0.0000
20001000-0002          2009-06-04 1000.0000            0.0000             0.0000
20001000-0003          2009-06-04 2000.0000            0.0000             0.0000
A                      2009-06-10 500.0000             -1400.0000         0.0000
A                      2009-06-17 200.0000             -1400.0000         0.0000
A                      2009-07-05 500.0000             -1400.0000         0.0000
a                      2009-09-20 9000.0000           -1400.0000          0.0000--用一句SQL语句重新计算为如下：goods_no              indate                   inqty             outqty         qty
 ---------------- ----------------------- ---------------- ----------------- -----------------
11012030-1010          2009-06-17 5000.0000        -2300.0000      2700.0000
11012030-1010          2009-06-17 100.0000         -2300.0000      100.0000
11012030-1010          2009-06-18 200.0000         -2300.0000      200.0000
11012030-1010          2009-06-18 100.0000         -2300.0000      100.0000
20001000-0002          2009-06-04 1000.0000         0.0000         1000.0000
20001000-0003          2009-06-04 2000.0000         0.0000         2000.0000
A                      2009-06-10 500.0000          -1400.0000     -900.0000
A                      2009-06-17 200.0000          -1400.0000     -700.0000
A                      2009-07-05 500.0000          -1400.0000     -200.0000
a                      2009-09-20 9000.0000         -1400.0000     8800.0000

Answer 1

SELECT *,CASE WHEN inqty+outqty>=0 THEN inqty+outqty ELSE inqty END AS QTY FROM TB?

Answer 2

select 
  goods_no,
  indate,
  inqty,
  outqty,
  qty=case when QTY>0 then INQTY else INQTY+OUTQTY end
from
  tb

Answer 3

不对，是上一行QTY大于0时，到INQTY。

Answer 4

要求重算是这样子的，QTY=INQTY+OUTQTY。
从第二行开始，如果第一行QTY>0,第二行QTY取INQTY。
如果不是，QTY=第一行QTY+INQTY+OUTQTY。以此类推。不知说得够不够明白？
不明白看一下数据应看得出来。

Answer 5

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([goods_no] varchar(13),[indate] datetime,[inqty] numeric(8,4),[outqty] numeric(8,4),[qty] numeric(5,4))
insert [tb]
select '11012030-1010','2009-06-17',5000.0000,-2300.0000,0.0000 union all
select '11012030-1010','2009-06-17',100.0000,-2300.0000,0.0000 union all
select '11012030-1010','2009-06-18',200.0000,-2300.0000,0.0000 union all
select '11012030-1010','2009-06-18',100.0000,-2300.0000,0.0000 union all
select '20001000-0002','2009-06-04',1000.0000,0.0000,0.0000 union all
select '20001000-0003','2009-06-04',2000.0000,0.0000,0.0000 union all
select 'A','2009-06-10',500.0000,-1400.0000,0.0000 union all
select 'A','2009-06-17',200.0000,-1400.0000,0.0000 union all
select 'A','2009-07-05',500.0000,-1400.0000,0.0000 union all
select 'a','2009-09-20',9000.0000,-1400.0000,0.0000select tid=identity(int,1,1),* into # from tbselect 
  goods_no,
  indate,
  inqty,
  outqty,
  qty=case when outqty+isnull((select sum(inqty) from # where goods_no=t.goods_no and tid<t.tid),0)>0 
             then inqty
           else outqty+(select sum(inqty) from # where goods_no=t.goods_no and tid<=t.tid)
      end
from
  # t
--测试结果:
/*
goods_no      indate                  inqty                                   outqty                                  qty
------------- ----------------------- --------------------------------------- --------------------------------------- ---------------------------------------
11012030-1010 2009-06-17 00:00:00.000 5000.0000                               -2300.0000                              2700.0000
11012030-1010 2009-06-17 00:00:00.000 100.0000                                -2300.0000                              100.0000
11012030-1010 2009-06-18 00:00:00.000 200.0000                                -2300.0000                              200.0000
11012030-1010 2009-06-18 00:00:00.000 100.0000                                -2300.0000                              100.0000
20001000-0002 2009-06-04 00:00:00.000 1000.0000                               0.0000                                  1000.0000
20001000-0003 2009-06-04 00:00:00.000 2000.0000                               0.0000                                  2000.0000
A             2009-06-10 00:00:00.000 500.0000                                -1400.0000                              -900.0000
A             2009-06-17 00:00:00.000 200.0000                                -1400.0000                              -700.0000
A             2009-07-05 00:00:00.000 500.0000                                -1400.0000                              -200.0000
a             2009-09-20 00:00:00.000 9000.0000                               -1400.0000                              8800.0000(10 行受影响)
*/

Answer 6

楼上兄弟正解，不过我是要更新回原表的。完善一下。送分下班了。TKS

Answer 7

楼主有点懒了...
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([goods_no] varchar(13),[indate] datetime,[inqty] numeric(18,4),[outqty] numeric(18,4),[qty] numeric(18,4))
insert [tb]
select '11012030-1010','2009-06-17',5000.0000,-2300.0000,0.0000 union all
select '11012030-1010','2009-06-17',100.0000,-2300.0000,0.0000 union all
select '11012030-1010','2009-06-18',200.0000,-2300.0000,0.0000 union all
select '11012030-1010','2009-06-18',100.0000,-2300.0000,0.0000 union all
select '20001000-0002','2009-06-04',1000.0000,0.0000,0.0000 union all
select '20001000-0003','2009-06-04',2000.0000,0.0000,0.0000 union all
select 'A','2009-06-10',500.0000,-1400.0000,0.0000 union all
select 'A','2009-06-17',200.0000,-1400.0000,0.0000 union all
select 'A','2009-07-05',500.0000,-1400.0000,0.0000 union all
select 'a','2009-09-20',9000.0000,-1400.0000,0.0000select tid=identity(int,1,1),* into # from tbupdate a
set a.qty=b.qty
from tb a,
(select 
  goods_no,
  indate,
  inqty,
  outqty,
  qty=case when outqty+isnull((select sum(inqty) from # where goods_no=t.goods_no and tid<t.tid),0)>0 
             then inqty
           else outqty+(select sum(inqty) from # where goods_no=t.goods_no and tid<=t.tid)
      end
from
  # t
) b
where a.goods_no=b.goods_no and a.indate=b.indate and a.inqty=b.inqty and a.outqty=b.outqtyselect * from tb
--测试结果:
/*
goods_no      indate                  inqty                                   outqty                                  qty
------------- ----------------------- --------------------------------------- --------------------------------------- ---------------------------------------
11012030-1010 2009-06-17 00:00:00.000 5000.0000                               -2300.0000                              2700.0000
11012030-1010 2009-06-17 00:00:00.000 100.0000                                -2300.0000                              100.0000
11012030-1010 2009-06-18 00:00:00.000 200.0000                                -2300.0000                              200.0000
11012030-1010 2009-06-18 00:00:00.000 100.0000                                -2300.0000                              100.0000
20001000-0002 2009-06-04 00:00:00.000 1000.0000                               0.0000                                  1000.0000
20001000-0003 2009-06-04 00:00:00.000 2000.0000                               0.0000                                  2000.0000
A             2009-06-10 00:00:00.000 500.0000                                -1400.0000                              -900.0000
A             2009-06-17 00:00:00.000 200.0000                                -1400.0000                              -700.0000
A             2009-07-05 00:00:00.000 500.0000                                -1400.0000                              -200.0000
a             2009-09-20 00:00:00.000 9000.0000                               -1400.0000                              8800.0000(10 行受影响)
*/