SQL选择一行直到条件

Question

我有两个表，如下：

第一表显示了所有订单：

table PURCHASE_ORDER:
ID   UNIT_#    PRICE   ITEM_KEY
1      2         3        1
2      3         2.5      1
3      1         3        1

第二个表显示了库存中可用的单元数：

table INVENTORY:
ID    ITEM_KEY    UNIT_AVAILABLE
1        1            5
2        2            7

现在的问题是如何计算库存中项目的平均成本。例如，项目1：

项目1 =（（1*3） （3*2.5） （1*3））/5
/*库存中项目的总数为5（1 3 1 = 5） */

有什么方法可以在纯Microsoft SQL？

Answer 1

您需要一个累积总和。让我假设您正在使用SQL Server 2012，因为它简化了代码。

select po.item_key,
       (sum(case when po.cumunits <= i.unit_available then po.[unit#]*po.price
                 when po.cumunits > i.unit_available and
                      po.cumunits - [unit#] < i.unit_available
                 then (i.unit_available - (po.cumunits - [unit#]))*po.price
                 else 0
            end) /
        sum(case when po.cumunits <= i.unit_available then po.[unit#]
                 when po.cumunits > i.unit_available and
                      po.cumunits - [unit#] < i.unit_available
                 then (i.unit_available - (po.cumunits - [unit#]))
                 else 0
            end)
      ) as avgprice
from (select po.*, sum([unit#]) over (partition by item_key order by id) as cumunits
      from purchase_order po
     ) po join
     inventory i
     on po.item_key = i.item_key
group po.item_key;

在SQL Server的早期版本中，您需要使用相关的子查询来进行累积总和。

Answer 2

基于表的设置方式

select po.*
     , i.*
     , AvgPrice = sum(po.[UNIT_#] * po.Price) over (partition by po.item_key)
                  /
                  i.unit_available
  from PURCHASE_ORDER po
       inner join INVENTORY i
         on (po.ITEM_KEY = i.ITEM_KEY);

应该做技巧