求一SQL語句

[wudefly]

表:
ID PartID(物料ID) OrderNo(訂單編號) wareqty(倉存數) qty(訂單數量)
-----------------------------------------------------------------------------------
1 1 3875 1000 75
2 1 4851 1000 80
3 1 3950 1000 750
4 2 3875 800 120
5 2 4851 800 600
6 2 3950 800 1200
要求查詢得如下:
PartID OrderNo NewQty
1 3875 925
1 3950 175
1 4851 95
2 3875 680
2 3950 -520
2 4851 -600

[wudefly]

謝謝 blackant2(乔峰)!!

update #tmp set @curnum=(case when @curid=partid then @curnum-qty else wareqty-qty end)
將上面這句改一下就得到我要的結果了:
update #tmp set @curnum=(case when @curid=partid then case when @curnum>0 then @curnum-qty else 0-qty end else wareqty-qty end),@curid=partid,qty=@curnum
是這樣的,一張訂單600pcs,針對這張訂單來說,如果倉存數沒有了,就欠料600pcs.
也就是說,針對一張訂單,欠料是不能大於其數量的.

[lizhizhe2000]

Select PartID,OrderNo,wareqtyqty as NewQty from TB

[wfcfan]

不懂..

[copico]

SELECT PartID,OrderNo,wareqty-qty as NewQty from table1

[lizhizhe2000]

Select PartID,OrderNo,wareqty-qty as NewQty from TB

[copico]

不对，重来

[copico]

-520
这个数据从哪来的啊

[Eddie005]

流水账这种东西，最好还是确认订单时就记录记录下来，现在再想用sql来实现，比较麻烦，需要写一个存储过程，declare一张临时表，先把记录按顺序查询出来并插入之，然后使用游标遍历并计算流水账

[leaohong]

select partid,orderno,sum(wareqty)-sum(qty) as newqty
from table
group by partid,orderno
order by partid

[HJS1820]

看不出來存在什么算法
幫頂

[hegang888888888]

Select PartID,OrderNo,sum(wareqtyqty) as NewQty from TB group by OrderNo

[blackant2]

declare @t table(
[ID] int
,PartID int
,OrderNo int
,wareqty int
,qty int
)

insert into @t
select 1 ,1, 3875, 1000, 75
union select 2,1, 4851 ,1000 ,80
union select 3 ,1, 3950 ,1000 ,750
union select 4 ,2 ,3875 ,800 ,120
union select 5 ,2 ,4851 ,800 ,600
union select 6 ,2 ,3950 ,800, 1200


declare @curid int
declare @curnum int
set @curid=0
set @curnum=0

select *
into #tmp
from @t
order by partid,orderno


update #tmp set @curnum=(case when @curid=partid then @curnum-qty else wareqty-qty end)
,@curid=partid
,qty=@curnum

select * from #tmp

drop table #tmp

[blackant2]

（所影响的行数为 6 行）


（所影响的行数为 6 行）


（所影响的行数为 6 行）

ID PartID OrderNo wareqty qty
----------- ----------- ----------- ----------- -----------
1 1 3875 1000 925
3 1 3950 1000 175
2 1 4851 1000 95
4 2 3875 800 680
6 2 3950 800 -520
5 2 4851 800 -1120

（所影响的行数为 6 行）