使用sql处理负值

Question

我有一个数据集，其中列出了未来产品库存的日期和数量。有时，我们的需求超过了我们未来的供应，我们最终得到了一个负的未来数量。我需要将未来的负数量计入之前的供应中，这样我们就不会因为过度销售供应而使问题变得更加复杂。

在下面的数据集中，我需要为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 server2008 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