帮其他人问:oracle?mysql?SqlServer?
--------------
DECLARE @KC_ BIGINT
SET @KC_=1000;
with test as(
select '2005' [订单号],'2.0' [料号],-1000 [数量]
union ALL
select '2004' [订单号],'2.0' [料号],-2000 [数量]
union ALL
select '2003' [订单号],'2.1' [料号],-500 [数量]
union ALL
select '2002' [订单号],'2.2' [料号],-500 [数量]
union ALL
select '2001' [订单号],'2.2' [料号],-1000 [数量]
union ALL
select '2000' [订单号],'2.2' [料号],-1000 [数量]
)
SELECT A.*,(CASE WHEN A.SL_+@KC_>=0 THEN A.SL_+@KC_ ELSE [数量] END)[KC] FROM (
SELECT *,SUM(数量) over(partition by 料号 ORDER BY 订单号 desc) [SL_] FROM TEST
) A
![1687934084238263.png image.png](/upload/wenda/20230628/1687934084238263.png)