处理与SQL负值(Handling negative values with sql)

我有一个数据集，其中列出的产品未来股票的日期和数量。 有时，我们的需求超过我们的未来供应，我们绕了一个负的未来量。 我需要因素，未来的负数量为以前的供应，所以我们不要被超卖我们的供应使问题更加复杂。

在下面的数据集，我需要通过需求，直到我留下了一个积极的数量正在施加负量环比上涨编写一份关于10-19：

"ID","SKU","DATE","SEASON","QUANTITY"
"1","001","2012-06-22","S12","1656"
"2","001","2012-07-13","F12","1986"
"3","001","2012-07-27","F12","-283"
"4","001","2012-08-17","F12","2718"
"5","001","2012-08-31","F12","-4019"
"6","001","2012-09-14","F12","7212"
"7","001","2012-09-21","F12","782"
"8","001","2012-09-28","F12","2073"
"9","001","2012-10-12","F12","1842"
"10","001","2012-10-19","F12","-12159"

我需要得到它这样的：

"ID","SKU","DATE","SEASON","QUANTITY"
"1","001","2012-06-22","S12","1656"
"2","001","2012-07-13","F12","152"

我已经看过使用while循环，以及外申请，但似乎无法找到一个方法来做到这一点呢。 任何帮助将非常感激。 这将需要为SQL Server 2008 R2工作。

再举一例：

"1","002","2012-07-13","S12","1980"
"2","002","2012-08-10","F12","-306"
"3","002","2012-09-07","F12","826"

会成为：

"1","002","2012-07-13","S12","1674"
"3","002","2012-09-07","F12","826"

Answer 1:

你似乎并没有得到很多答案 - 所以这里的东西，如果你不会得到正确的“如何，做纯SQL”。 忽略此解决方案，如果有什么事SQLish - 它只是一个防御性编码，不优雅。

如果你想获得的所有数据的总和与同一赛季为什么删除重复记录 - 只要得到它之外，运行foreach循环，和与同一个赛季价值，更新表与正确的价值观所有数据，并删除不必要的条目。 下面是做到这一点（伪）的方法之一：

productsArray = SELECT * FROM products
processed = array (associative)
foreach product in productsArray:
  if product[season] not in processed:
    processed[season] = product[quantity]
    UPDATE products SET quantity = processed[season] WHERE id = product[id]
  else:
    processed[season] = processed[season] + product[quantity]
    DELETE FROM products WHERE id = product[id]

Answer 2:

这里是一个CROSS APPLY -测试

SELECT b.ID,SKU,b.DATE,SEASON,QUANTITY
FROM (
  SELECT SKU,SEASON, SUM(QUANTITY) AS QUANTITY
  FROM T1 
  GROUP BY SKU,SEASON
  ) a
CROSS APPLY (
  SELECT TOP 1 b.ID,b.Date FROM T1 b
  WHERE a.SKU = b.SKU AND a.SEASON = b.SEASON
  ORDER BY b.ID ASC
  ) b
  ORDER BY ID ASC

文章来源: Handling negative values with sql