如何在sql中按组查找运行总数的最小值？

我长期使用excel和powerquery，但在sql方面，我完全是个脑残。
我的项目的目标是找到一个最小的价值在一个总运行，按组。
我已经弄清楚了怎么做跑垒。我的下一个想法是对我的running total查询运行另一个查询，其中我对running total列运行另一个聚合函数以返回最小值。当我这样做的时候，查询将永远输出，然后返回一个错误。。。
我将注意到，我正在msaccess中创建sql代码，因为我目前无法访问可以在工作中使用的sql服务器。
因此，首先我创建一个联合查询，将多个表组合在一起，并将该查询称为“combined”：

SELECT [ItemCode], '1/1/2010' as [Date], 'IM' as [Type], [WarehouseCode], [QuantityOnHand] as [Qty]
    FROM [IM_ItemWarehouse]

    UNION ALL

    SELECT [ItemCode], [RequiredDate] as [Date], 'PO' as [Type], [WarehouseCode], [QuantityOrdered] - [QuantityReceived] as [Qty]
    FROM [PO_PurchaseOrderDetail]
    WHERE [QuantityOrdered] - [QuantityReceived] > 0

    UNION ALL 

    SELECT [ItemCode], [PromiseDate] as [Date], 'SO' as [Type], [WarehouseCode], ([QuantityOrdered] - 
    [QuantityShipped])*-1 as [Qty]
    FROM [SO_SalesOrderDetail]
    WHERE [QuantityOrdered] - [QuantityShipped] > 0;

然后我去老家，使用嵌套的select语句来计算我的运行总数，并将此查询称为“runtotal”：

SELECT t1.[ItemCode], t1.[WarehouseCode], t1.[Date], sum(t1.[Qty]) AS TotalByDate, (SELECT
    sum(t2.[Qty])
    FROM Combined t2
    WHERE t2.[ItemCode] = t1.[ItemCode] AND t2.[WarehouseCode] = t1.[WarehouseCode] AND t2.[Date] <= t1.[Date]) AS RunningTotal
    FROM Combined t1
    GROUP BY t1.[ItemCode], t1.[WarehouseCode], t1.[Date]
    ORDER BY t1.[ItemCode], t1.[WarehouseCode], t1.[Date];

然后…卡住了…这是我尝试过的，但到目前为止，查询只会永远旋转或返回错误：

SELECT [ItemCode], [WarehouseCode], min([RunningTotal])
    FROM RunTotal
    GROUP BY [ItemCode], [WarehouseCode];

提前感谢您提供的任何帮助…我知道使用窗口函数可以更有效地运行总计算，但由于我在ms access中编写这些sql语句，因此我无法访问像over和partition by之类的有趣的东西。。。
基础数据示例：

Style   Type    Whs Date        Qty
widget  On Hand NVR 1/1/2010    100
widget  On SO   NVR 7/15/2020   -30
widget  On PO   NVR 7/18/2020   50
widget  On SO   NVR 7/19/2020   -10
widget  On SO   NVR 7/20/2020   -60
gizmo   On Hand NVR 1/1/2010    100
gizmo   On SO   NVR 7/15/2020   -100
gizmo   On PO   NVR 7/18/2020   50
gizmo   On SO   NVR 7/19/2020   -20
gizmo   On SO   NVR 7/20/2020   -30

示例运行总计：

Style   Type    Whs Date        Qty  RunTotal
widget  On Hand NVR 1/1/2010    100  100 
widget  On SO   NVR 7/15/2020   -30  70 
widget  On PO   NVR 7/18/2020   50   120 
widget  On SO   NVR 7/19/2020   -10  110 
widget  On SO   NVR 7/20/2020   -60  50 
gizmo   On Hand NVR 1/1/2010    100  100 
gizmo   On SO   NVR 7/15/2020   -90  10 
gizmo   On PO   NVR 7/18/2020   50   60 
gizmo   On SO   NVR 7/19/2020   -20  40 
gizmo   On SO   NVR 7/20/2020   -10  30

最终结果示例：

Style   Whs MinRunTotal
widget  NVR 50
gizmo   NVR 10

从语法上讲，您的第一个查询不能在ms access中编译，因为runningtotal相关聚合表达式应该包含在 GROUP BY 条款。但实际上，在中可能不允许相关子查询 GROUP BY 而且您也不希望在聚合期间按此秩计算进行分组。另外，在ms access中，在底层数据库上运行复杂的操作 UNION 查询不会产生性能问题。
考虑以下设置：
将基础联合查询转换为临时表：

SELECT * INTO mytemptable FROM myUnionQuery

计算运行总计（准聚合）。

SELECT t.[ItemCode], t.[WarehouseCode], t.[Date], 
       (SELECT SUM(sub.[Qty]) FROM myTempTable sub
        WHERE sub.[ItemCode] = t.[ItemCode] 
          AND sub.[WarehouseCode] = t.[WarehouseCode]
          AND sub.[Date] <= t.[Date]) AS RunningQtyTotal
FROM myTempTable t;

骨料 [ItemCode] 以及 [WarehouseCode] 水平：

SELECT q.[ItemCode], q.[WarehouseCode], MIN(q.[RunningQtyTotal]) AS MinRunQtyTotal
FROM mySelectQuery q
GROUP BY q.[ItemCode], q.[WarehouseCode]

如何在sql中按组查找运行总数的最小值？

1条答案

相关问题

热门标签

最新问答