时间戳

首页 > 解决方案 > SQL 根据汇总和返回有限的行

sql - SQL 根据汇总和返回有限的行

问题描述

我想从一个表中返回一些行，其总和取决于另一个表中一行的值：

场景：特定项目数量的销售订单。该项目位于多个 Bin 位置。店员需要被引导到最古老的材料。

我可以创建一个查询，列出 Bin、bin 中的 Qty 并按年龄（从最老到最年轻）列出它们 - 到目前为止一切都很好，但是说订单是 100 个单位，每个 bin 中有 50 个左右的单位并且有 40 个箱子，那么我不想列出所有箱子，只列出最旧的两个 - 足以满足订单。

我怎么做？

只是请求的更多信息 DB = MS SQL 2016 示例数据：以下是显示 Bin、该 bin 中的数量和老化日期的特定项目的数据：

Bin#、数量、日期

1,40,2018-05-15

3,45,2018-05-15

8,45,2018-02-10

12,45,2017-11-11

13,45,2018-02-10

15,45,2017-09-02

18,20,2017-09-02

销售订单是 100 个这样的项目，我们要选择 FIFO（先进先出），所以我要返回的结果是：

18,20,2017-09-02

15,45,2017-09-02

12,45,2017-11-11

这三个箱总共包含 110 个单位，因此足以满足销售订单。请注意，订单是日期，然后是数量

当前的实际查询是：

select 
    [OrderHed].[OrderNum] as [OrderHed_OrderNum],
    [OrderRel].[OrderLine] as [OrderRel_OrderLine],
    [Part].[PartNum] as [Part_PartNum],
    [Part].[PartDescription] as [Part_PartDescription],
    [OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
    [PartBin].[BinNum] as [PartBin_BinNum],
    [PartBin].[OnhandQty] as [PartBin_OnhandQty],
    [PartLot].[FirstRefDate] as [PartLot_FirstRefDate]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on 
    OrderHed.Company = OrderDtl.Company
    and OrderHed.OrderNum = OrderDtl.OrderNum
inner join Erp.OrderRel as OrderRel on 
    OrderDtl.Company = OrderRel.Company
    and OrderDtl.OrderNum = OrderRel.OrderNum
    and OrderDtl.OrderLine = OrderRel.OrderLine
    and ( OrderRel.OpenRelease = True  )

left outer join Erp.PartBin as PartBin on 
    OrderRel.Company = PartBin.Company
    and OrderRel.WarehouseCode = PartBin.WarehouseCode
    and ( not PartBin.BinNum like 'Q'  )

inner join Erp.Part as Part on 
    OrderDtl.Company = Part.Company
    and OrderDtl.PartNum = Part.PartNum
right outer join Erp.Part as Part
    and 
    PartBin.Company = Part.Company
    and PartBin.PartNum = Part.PartNum
inner join Erp.PartLot as PartLot on 
    PartBin.Company = PartLot.Company
    and PartBin.PartNum = PartLot.PartNum
    and PartBin.LotNum = PartLot.LotNum
where (OrderHed.OrderNum = @SalesOrder)
order by OrderDtl.OrderLine, PartLot.FirstRefDate, PartBin.OnhandQty

标签： sqlsql-server

解决方案

  1. 您可以选择日期小于或等于日期的所有 bin 的数量之和大于或等于您的目标数量（例如 50）的最小日期。

    SELECT *
       FROM bin b
       WHERE b.date <= (SELECT min(bb.date)
                               FROM bin bb
                               WHERE (SELECT sum(bbb.qty)
                                             FROM bin bbb
                                             WHERE bbb.date <= bb.date) >= 50)
ORDER BY b.date,
         b.bin#;

    然而，这种方法可以包括比必要更多的箱。如果从最年轻的日期开始的 bin 数量超过了满足目标数量所需的数量，则无论如何都将包括它们。因此，为订单挑选商品的人必须从这些箱子中进行选择。但至少 FIFO 规则是这样保持的，无论如何人们都必须清点物品，不能盲目地从退回的垃圾箱中挑选。

    SQL Fiddle（注意，我添加了 bin 20 来演示上述问题。）

  2. 我提到的问题 1. 如果你给所有的箱子一个按日期排序的数字，就可以避免。然后不会有与日期一样的重复值。您可以通过ROW_NUMBER()在 CTE 中使用来介绍此号码。然后使用与 1 中相同的逻辑从 CTE 中选择。但应用于行号而不是日期。

    WITH cte
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY b.date) row#,
       b.*
       FROM bin b
)
SELECT *
       FROM cte c
       WHERE c.row# <= (SELECT min(cc.row#)
                               FROM cte cc
                               WHERE (SELECT sum(ccc.qty)
                                             FROM cte ccc
                                             WHERE ccc.row# <= cc.row#) >= 50)
ORDER BY c.date,
         c.bin#;

    SQL Fiddle（请注意，我再次添加了 bin 20 以证明 1. 中提到的问题已得到解决。）

然而，这两种方法都不一定会产生“最佳”的垃圾箱集。例如，可能有一组带有正确日期的箱，它们正好存放订购的物品数量，但该组只是偶然返回的。也可能有一组 bin 的基数小于返回集合中的那个。

推荐阅读