文章/答案/技术大牛

发布

社区首页 >问答首页 >SQL递归CTE“何处使用”/ BOM爆炸

问SQL递归CTE“何处使用”/ BOM爆炸
EN

Stack Overflow用户

提问于 2015-05-14 09:25:41

回答 1查看 4.6K关注 0票数 1

我想做的只是简单的从BOM表(物料清单)中的一个项目，从BOMVERSION获得它的相关组件/产品，然后对于那些相关的组件/产品，将它们的相关组件和产品等降到7个级别。然后，我希望将结果转到0, 1, 2, 3, 4, 5, 6, 7列中，以便将相关项放入列中。

请参阅附件中的示例数据和代码。我正在使用dynamics 2012 R2，但是这个示例可以应用于任何使用材料/产品的系统。但是，我无法完成我的查询(我知道我没有为简单起见而忽略的数据辅助和分区)。关系是BOM表中的一个项，它与BOMVERSION中的项通过BOMID相关。

更新:我已经简化了数据等，以使尽可能简单/清晰，这样材料，木材，金属，玻璃可以进入各种产品和材料本身，并可以组合成产品或材料。因此，我想从一个基本组件开始，然后按级别将关系分解。

DDL + DML：

    USE tempdb;

IF OBJECT_ID('tempdb..#BOM') IS NOT NULL
    DROP TABLE #BOM;

CREATE TABLE #BOM
    (
      ITEMID NVARCHAR(10) ,
      BOMID NVARCHAR(10) ,
      MATERIALNAME NVARCHAR(10)
    );

INSERT  INTO #BOM
VALUES  ( N'113621', -- ITEMID - nvarchar(10)
          N'1',  -- BOMID - nvarchar(10)
          N'Wood'  -- MATERIALNAME - nvarchar(10)
          );

INSERT  INTO #BOM
VALUES  ( N'234517', -- ITEMID - nvarchar(10)
          N'2',  -- BOMID - nvarchar(10)
          N'Metal'  -- MATERIALNAME - nvarchar(10)
          );

INSERT  INTO #BOM
VALUES  ( N'378654', -- ITEMID - nvarchar(10)
          N'3',  -- BOMID - nvarchar(10)
          N'Glass'  -- MATERIALNAME - nvarchar(10)
          );

IF OBJECT_ID('tempdb..#BOMVERSION') IS NOT NULL
    DROP TABLE #BOMVERSION;

CREATE TABLE #BOMVERSION
    (
      ITEMID NVARCHAR(10) ,
      BOMID NVARCHAR(10) ,
      NAME NVARCHAR(20)
    );

INSERT  INTO #BOMVERSION
VALUES  ( N'113477', -- ITEMID - nvarchar(10)
          N'1', -- BOMID - nvarchar(10)
          N'Oak'  -- NAME - nvarchar(10)
          );

INSERT  INTO #BOMVERSION
VALUES  ( N'113608', -- ITEMID - nvarchar(10)
          N'1', -- BOMID - nvarchar(10)
          N'Pine'  -- NAME - nvarchar(10)
          );

INSERT  INTO #BOMVERSION
VALUES  ( N'113622', -- ITEMID - nvarchar(10)
          N'1', -- BOMID - nvarchar(10)
          N'Wood Table'  -- NAME - nvarchar(10)
          );

INSERT  INTO #BOMVERSION
VALUES  ( N'113683', -- ITEMID - nvarchar(10)
          N'2', -- BOMID - nvarchar(10)
          N'Aluminium'  -- NAME - nvarchar(10)
          );

INSERT  INTO #BOMVERSION
VALUES  ( N'113689', -- ITEMID - nvarchar(10)
          N'2', -- BOMID - nvarchar(10)
          N'Steel'  -- NAME - nvarchar(10)
          );

INSERT  INTO #BOMVERSION
VALUES  ( N'113693', -- ITEMID - nvarchar(10)
          N'2', -- BOMID - nvarchar(10)
          N'Metal table'  -- NAME - nvarchar(10)
          );

INSERT  INTO #BOMVERSION
VALUES  ( N'113694', -- ITEMID - nvarchar(10)
          N'3', -- BOMID - nvarchar(10)
          N'Glass'  -- NAME - nvarchar(10)
          );

INSERT  INTO #BOMVERSION
VALUES  ( N'113695', -- ITEMID - nvarchar(10)
          N'3', -- BOMID - nvarchar(10)
          N'Glass BookCase'  -- NAME - nvarchar(10)
          );

--Query

WITH    BOM1
          AS ( SELECT   B.ITEMID AS BITEMID ,
                        BV.ITEMID AS BVITEMID ,
                        B.MATERIALNAME ,
                        B.BOMID
               FROM     #BOM AS B
                        JOIN #BOMVERSION AS BV ON BV.BOMID = B.BOMID
             ),
        EXPLODE
          AS ( SELECT   B.BITEMID ,
                        B.MATERIALNAME ,
                        B.BVITEMID ,
                        B.BOMID ,
                        0 AS [Level]
               FROM     BOM1 AS B
               UNION ALL
               SELECT   B.BITEMID ,
                        E.MATERIALNAME ,
                        E.BVITEMID ,
                        E.BOMID ,
                        [E].[Level] + 1
               FROM     EXPLODE AS E
                        JOIN BOM1 AS B ON B.BOMID = E.BOMID
               WHERE    E.Level <= 6   --narrowing levels                   
             )
    SELECT  *
    FROM    EXPLODE PIVOT ( MAX(BVITEMID) FOR Level IN ( [0], [1], [2], [3],
                                                         [4], [5], [6], [7] ) ) AS PVTBOM;

输出如下

dynamics-ax-2012-r2

sql-server

Stack Overflow用户

回答已采纳

发布于 2015-05-14 09:40:25

这只是为了消除错误并得到结果，但我不知道结果是否正确：

WITH    BOM1
          AS ( SELECT   B.ITEMID AS BITEMID ,
                        BV.ITEMID AS BVITEMID ,
                        B.BOMID
               FROM     #BOM AS B
                        JOIN #BOMVERSION AS BV ON BV.BOMID = B.BOMID
             ),
        EXPLODE
          AS ( SELECT   B.BITEMID ,
                        B.BVITEMID ,
                        B.BOMID ,
                        0 AS [Level]
               FROM     BOM1 AS B
               UNION ALL
               SELECT   B.BITEMID ,
                        E.BVITEMID ,
                        E.BOMID ,
                        [E].[Level] + 1
               FROM     EXPLODE AS E
                        JOIN BOM1 AS B ON B.BOMID = E.BOMID
               WHERE e.Level <= 6   --narrowing levels                   
             )


    SELECT  *
    FROM    EXPLODE PIVOT ( MAX(BVITEMID) FOR Level IN ( [0], [1], [2], [3],
                                                         [4], [5], [6], [7] ) ) AS PVTBOM

你有maxrecursion = 7 -这里不是缩小水平的地方。级别在CTE中的递归部分中缩小。

我怀疑您的递归没有很好的组织，所以您能简化您的测试数据吗？只需留下几行并显示预期的输出。

EDIT1:

WITH    p AS ( SELECT   *
               FROM     ( SELECT    itemid ,
                                    bomid ,
                                    ROW_NUMBER() OVER ( PARTITION BY Bomid ORDER BY itemid ) rn
                          FROM      #BOMVERSION
                        ) t PIVOT ( MAX(itemid) FOR rn IN ( [1], [2], [3], [4],
                                                            [5], [6], [7], [8] ) ) p
             )
    SELECT  *
    FROM    #bom b
JOIN p ON b.bomid = p.bomid

EDIT2:

;WITH    cte
          AS ( SELECT   b.itemid AS originalitem ,
                        b.bomid AS originalbom ,
                        b.bomid ,
                        bv.itemid AS parent ,
                        0 AS level
               FROM     #BOM b
                        JOIN #BOMVERSION bv ON bv.bomid = b.bomid
               UNION ALL
               SELECT   c.originalitem ,
                        c.originalbom ,
                        b.bomid ,
                        bv.itemid ,
                        c.level + 1
               FROM     cte c
                        JOIN #BOM b ON c.parent = b.itemid
                        JOIN #BOMVERSION bv ON bv.bomid = b.bomid
               WHERE    c.level <= 6
             ),
        tree
          AS ( SELECT   originalitem ,
                        originalbom ,
                        parent ,
                        level
               FROM     cte
             )
    SELECT  *
    FROM    tree PIVOT ( MAX(parent) FOR level IN ( [0], [1], [2], [3], [4], [5], [6], [7] ) ) AS p

票数 2

查看全部 1 条回答

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/30233716

复制

相似问题

问SQL递归CTE“何处使用”/ BOM爆炸
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL递归CTE“何处使用”/ BOM爆炸EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL递归CTE“何处使用”/ BOM爆炸
EN