sql - 如何在 SQL 中执行嵌套循环？

问题描述

我正在尝试计算项目的数量，其中每个项目可能有孩子的数量也将包括在内。就深度而言，树没有具体的限制，所以我需要能够循环直到没有找到一个项目的子项，然后转到该级别的下一个项目并继续计算项目计数的总和。

我可以使用以下 SQL 计算初始级别的项目总和，但我不知道如何正确循环以添加每个项目的任何子项的数量：

SELECT SUM(ShippingUnitMaterial.Quantity)
       FROM ShippingUnitMaterial
       WHERE ShippingUnitMaterial.ShippingUnitID =                           
             ShippingUnit.ShippingUnitID)
       AS TotalMaterialQty

我的表是这样构造的，因此一个运输单位有一个 ID 和一个 ParentID。数量 SUM 的计算如前面的 SUM 语句所示，然后我需要执行查找并为每个子项执行相同的计算（添加到主 SUM）。

在伪代码中它将是：

LOOP
FOR EACH ShippingUnitID in an input list of ShippingUnitIDs
    SUM the ShippingUnitMaterial.Quantity for all records in ShippingUnitMaterial
    WHERE ShippingUnitMaterial.ShippingUnitID == ShippingUnit.ShippingUnitID
    create a list, ChildList, of all ShippingUnitIDs where the ParentID == ShippingUnit.ShippingUnitID
    IF ChildList is not empty, call MAIN LOOP with the list
END LOOP

我可以很容易地用 C# 编写它，但是在复制 SQL 中的逻辑时遇到了麻烦。

我应该得到所有树枝数量的总和，其中每个分支都以输入列表中的 ID 开头。

示例输入列表：

3093, 3096

ShippingUnitMaterial 表的示例数据：

ShippingUnitMaterialID  DeliveryID  ShippingUnitID  Quantity
4204                    1           3093            1
4205                    1           3094            2
4207                    3           3099            7
4208                    3           3096            4

ShippingUnitSU 表的示例数据：

ShippingUnitSUID    DeliveryID  ParentShippingUnitID    ShippingUnitID
205                 2391        3097                    3093
206                 2391        3093                    3094
207                 2391        3093                    3099
208                 2391        3313                    3096

所需的输出将是：

For input 3093, 10
For input 3096, 4

标签： sqlsql-servertsqlstored-procedures

正如其他人所提到的，您在这里想要的是一个递归公用表表达式 (rCTE)，通过父子关系进行递归，然后进行聚合。这导致以下结果：

CREATE TABLE dbo.ShippingUnitMaterial (ShippingUnitMaterialID int,
                                       DeliveryID int,
                                       ShippingUnitID int,
                                       Quantity int);

CREATE TABLE dbo.ShippingUnitSU (ShippingUnitSUID int,
                                 DeliveryID int,
                                 ParentShippingUnitID int,
                                 ShippingUnitID int);

INSERT INTO dbo.ShippingUnitMaterial
VALUES (4204,1,3093,1),
       (4205,1,3094,2),
       (4207,3,3099,7),
       (4208,3,3096,4);

INSERT INTO dbo.ShippingUnitSU
VALUES (205,2391,3097,3093),
       (206,2391,3093,3094),
       (207,2391,3093,3099),
       (208,2391,3313,3096);
GO

WITH Shippings AS (
    SELECT SU.ShippingUnitID AS StartID,
           NULL AS ParentID,
           SU.ShippingUnitID AS ShippingID
    FROM dbo.ShippingUnitSU SU
    WHERE SU.ShippingUnitID IN (3093,3096)
    UNION ALL
    SELECT S.StartID,
           S.ShippingID AS ParentID,
           SU.ShippingUnitID AS ShippingID
    FROM dbo.ShippingUnitSU SU
         JOIN Shippings S ON SU.ParentShippingUnitID = S.ShippingID)
SELECT S.StartID,
       SUM(UM.Quantity) AS TotalQuantity
FROM Shippings S
     JOIN dbo.ShippingUnitMaterial UM ON S.ShippingID = UM.ShippingUnitID
GROUP BY S.StartID;


GO

DROP TABLE dbo.ShippingUnitSU;
DROP TABLE dbo.ShippingUnitMaterial;

sql - 如何在 SQL 中执行嵌套循环？

问题描述

解决方案

推荐阅读