时间戳

首页 > 解决方案 > 如何连接 2 个表来分配项目？

sql - 如何连接 2 个表来分配项目？

问题描述

我创建了 2 个包含库存信息（项目、位置、数量）的表。其中之一NeedInv具有需要X项目数量的项目/位置。另一个HaveInv有物品数量过多的物品/位置X。

我正在尝试加入或组合这 2 个表以输出应在哪些位置之间转移哪些项目。我有针对单个分发位置执行此操作的代码，并且我尝试对其进行修改并添加逻辑以使其与多个分发位置一起使用，但在某些情况下它仍然失败。

我创建了一个[sqlfiddle] 1，但示例数据如下： 

CREATE TABLE NeedInv
    (item int, location varchar(1), need int)

INSERT INTO NeedInv
    (item, location, need)
VALUES
    (100, 'A', 4), (100, 'B', 0), (100, 'C', 2), (200, 'A', 0), (200, 'B', 1), (200, 'C', 1), (300, 'A', 3), (300, 'B', 5), (300, 'C', 0)

CREATE TABLE HaveInv
    (item int, location varchar(1), have int)

INSERT INTO HaveInv
    (item, location, have)
VALUES
    (100, 'A', 0), (100, 'B', 3), (100, 'C', 0), (100, 'D', 3), (200, 'A', 1), (200, 'B', 0), (200, 'C', 0), (200, 'D', 1), (300, 'A', 0), (300, 'B', 0), (300, 'C', 20), (300, 'D', 5)

CREATE TABLE DesiredOutput
    (item int, SourceLocation varchar(1), TargetLocation varchar(1), Qty int)

INSERT INTO DesiredOutput
    (item, SourceLocation, TargetLocation, Qty)
VALUES
    (100, 'B', 'A', 3), (100, 'D', 'A', 1), (100, 'D', 'C', 2), (200, 'A', 'B', 2), (200, 'A', 'C', 3), (200, 'D', 'C', 1), (300, 'C', 'A', 3), (300, 'C', 'B', 3)

由于加入表格，我试图输出类似的内容： 

+------+----------------+----------------+-----+
| item | SourceLocation | TargetLocation | Qty |
+------+----------------+----------------+-----+
|  100 | B              | A              |   3 |
|  100 | D              | A              |   1 |
|  100 | D              | C              |   2 |
|  200 | A              | B              |   2 |
|  200 | A              | C              |   3 |
|  200 | D              | C              |   1 |
|  300 | C              | A              |   3 |
|  300 | C              | B              |   3 |
+------+----------------+----------------+-----+

我当前加入 2 个表的查询如下所示： 

select 
    n.*,
    (case when Ord <= Remainder and (RemaingNeed > 0 and RemaingNeed < RemainingInv) then Allocated + RemaingNeed else case when RemaingNeed < 0 then 0 else Allocated end end) as NeedToFill
from (
select
    n.*,
    row_number() over(partition by item order by RN, (case when need > Allocated then 0 else 1 end)) as Ord,
    n.TotalAvail - sum(n.Allocated) over (partition by item) as Remainder
from (
select 
    n.*,
    n.TotalAvail - sum(n.Allocated) over (partition by item order by RN) as RemainingInv,
    n.need - sum(n.Allocated) over (partition by item, location order by RN) as RemaingNeed
from (
  select
    n.*,
    case when Proportional > need then need else Proportional end as Allocated
  from (
    select
      row_number() over(order by need desc) as RN,
      n.*,
      h.location as Source,
      h.have,
      h.TotalAvail,
      convert(int, floor(h.have * n.need * 1.0 / n.TotalNeed), 0) as Proportional
    from (
      select n.*, sum(need) over (partition by item) as TotalNeed
      from NeedInv n) n
    join (select h.*, sum(have) over (partition by item) as TotalAvail from HaveInv h) h
      on n.item = h.item
      and h.have > 0
    ) n
  ) n
) n
) n
where n.need > 0

它似乎适用于大多数情况，除非Allocated设置为零，但仍有可以转移的项目。这可以在 item 200 1中看到，其中 locationB只需要1但将要接收2项目，而 locationC也需要1item 将收到0。

任何帮助/指导将不胜感激！

标签： sqlsql-servertsql

解决方案

Your query looks a little complicated for what it needs to do, IMO.

As far as I can tell, this is just a simple matter of building the logic into a query using running totals of inventory. Essentially, it's just a matter of building in rules such that if what you need can be taken from a source location, you take it, otherwise you take as much as possible.

For example, I believe the following query contains the logic required: 

SELECT N.Item,
       SourceLocation = H.Location,
       TargetLocation = N.Location,
       Qty = 
        CASE 
            WHEN N.TotalRunningRequirement <= H.TotalRunningInventory -- If the current source location has enough stock to fill the request.
            THEN 
                CASE
                    WHEN N.TotalRunningRequirement - N.Need < H.TotalRunningInventory - H.Have -- If stock required has already been allocated from elsewhere.
                    THEN N.TotalRunningRequirement - (H.TotalRunningInventory - H.Have) -- Get the total running requirement minus stock allocated from elsewhere.
                    ELSE N.Need -- Otherwise just take how much is needed.
                END
            ELSE N.Need - (N.TotalRunningRequirement - H.TotalRunningInventory) -- Current source doesn't have enough stock to fulfil need, so take as much as possible.
        END
FROM
( 
    SELECT *, TotalRunningRequirement = SUM(need) OVER (PARTITION BY item ORDER BY location)
    FROM NeedInv
    WHERE need > 0
) AS N
JOIN 
(
    SELECT *, TotalRunningInventory = SUM(have) OVER (PARTITION BY item ORDER BY location)
    FROM HaveInv
    WHERE have > 0  
) AS H
    ON H.Item = N.Item
    AND H.TotalRunningInventory - (N.TotalRunningRequirement - N.need) > 0 -- Join if stock in source location can be taken
    AND H.TotalRunningInventory - H.Have - (N.TotalRunningRequirement - N.need) < N.TotalRunningRequirement
;

Note: Your desired output doesn't seem to match your sample data for Item 200 as far as I can tell.

推荐阅读