SQL——练习：往下展开BOM

--练习：往下展开BOM
IF EXISTS
(
    SELECT *
    FROM tempdb.dbo.sysobjects
    WHERE id = OBJECT_ID(N'tempdb.dbo.#temp_bom')
) --是否存在该临时表
    DROP TABLE #temp_bom --存在则删除

CREATE TABLE #temp_bom --创建临时表
(
    ROOT_ITEM_ID UNIQUEIDENTIFIER,   --根品号
    TOTAL_SEQ NVARCHAR(200),         --层级关系
    SEQ INT,                         --序号
    LEV INT,                         --层次
    BOM_ID UNIQUEIDENTIFIER,
    BOM_D_ID UNIQUEIDENTIFIER,
    PARENT_ITEM_ID UNIQUEIDENTIFIER, --父级品号
    ITEM_ID UNIQUEIDENTIFIER,        --自身品号
    EFFECTIVE_DATE DATE,             --生效日期
    EXPRITY_DATE DATE,               --失效日期
    QTY_PER DECIMAL(16, 6)           --组成用量
)

DECLARE @ITEM_CODE VARCHAR(20),
        @CurrentLevel AS INT,
        @Level INT,
        @IsBottom INT -- 0-多阶，1-尾阶

SELECT @ITEM_CODE = '210010001', --130010005
       @Level = 20,
       @IsBottom = 0

INSERT INTO #temp_bom
(
    ROOT_ITEM_ID,
    TOTAL_SEQ,
    SEQ,
    LEV,
    BOM_ID,
    BOM_D_ID,
    PARENT_ITEM_ID,
    ITEM_ID,
    EFFECTIVE_DATE,
    EXPRITY_DATE,
    QTY_PER
)
SELECT b.ITEM_ID AS ROOT_ITEM_ID,
       '0001' AS TOTAL_SEQ,
       1 AS SEQ,
       0 AS LEV,
       b.BOM_ID AS BOM_ID,
       '00000000-0000-0000-0000-000000000000' AS BOM_D_ID,
       '00000000-0000-0000-0000-000000000000' AS PARENT_ITEM_ID,
       b.ITEM_ID AS ITEM_ID,
       CAST('1900-01-01' AS DATE) AS EFFECTIVE_DATE,
       CAST('1900-01-01' AS DATE) AS EXPRITY_DATE,
       1 AS QTY_PER
FROM dbo.BOM AS b
    INNER JOIN dbo.ITEM AS i
        ON i.ITEM_BUSINESS_ID = b.ITEM_ID
WHERE i.ITEM_CODE = @ITEM_CODE
      AND b.Owner_Org_ROid = 'C8BAE02C-0A60-4FD0-46A9-12140D245E5D'



SELECT @CurrentLevel = 1

WHILE @CurrentLevel <= @Level
BEGIN

    INSERT INTO #temp_bom
    (
        ROOT_ITEM_ID,
        TOTAL_SEQ,
        SEQ,
        LEV,
        BOM_ID,
        BOM_D_ID,
        PARENT_ITEM_ID,
        ITEM_ID,
        EFFECTIVE_DATE,
        EXPRITY_DATE,
        QTY_PER
    )
    SELECT tb.ROOT_ITEM_ID,                                                                          -- ROOT_ITEM_ID - uniqueidentifier
           tb.TOTAL_SEQ,                                                                             -- TOTAL_SEQ - nvarchar(200)
           (ROW_NUMBER() OVER (PARTITION BY tb.ROOT_ITEM_ID, b.ITEM_ID ORDER BY bd.SequenceNumber)), -- SEQ - int
           @CurrentLevel,                                                                            -- LEV - int
           b.BOM_ID,                                                                                 -- BOM_ID - uniqueidentifier
           bd.BOM_D_ID,                                                                              -- BOM_D_ID - uniqueidentifier
           b.ITEM_ID,                                                                                -- PARENT_ITEM_ID - uniqueidentifier
           bd.SOURCE_ID_ROid,                                                                        -- ITEM_ID - uniqueidentifier
           bd.EFFECTIVE_DATE,                                                                        -- EFFECTIVE_DATE - date
           bd.EXPRITY_DATE,                                                                          -- EXPRITY_DATE - date
           bd.QTY_PER                                                                                -- QTY_PER - decimal(16, 6)
    FROM #temp_bom AS tb
        INNER JOIN dbo.BOM AS b
            ON b.ITEM_ID = tb.ITEM_ID
               AND b.Owner_Org_ROid = 'C8BAE02C-0A60-4FD0-46A9-12140D245E5D'
        INNER JOIN dbo.BOM_D AS bd
            ON bd.BOM_ID = b.BOM_ID
    WHERE tb.LEV = @CurrentLevel - 1
          AND
          (
              bd.EXPRITY_DATE = '9998-12-31 00:00:00.0000000'
              OR bd.EXPRITY_DATE >= GETDATE()
          )
          AND
          (
              bd.EFFECTIVE_DATE = '1900-01-01 00:00:00.0000000'
              OR bd.EFFECTIVE_DATE <= GETDATE()
          )
    ORDER BY tb.ROOT_ITEM_ID,
             b.ITEM_ID,
             bd.SequenceNumber

    IF @@ROWCOUNT = 0
    BEGIN
        BREAK
    END

    UPDATE tb
    SET tb.TOTAL_SEQ = tb.TOTAL_SEQ + '.' + RIGHT('1000' + CAST(tb.SEQ AS VARCHAR(4)), 4)
    FROM #temp_bom AS tb
    WHERE tb.LEV = @CurrentLevel

    SELECT @CurrentLevel += 1
END

IF @IsBottom = 1
BEGIN
    DELETE tb
    FROM #temp_bom AS tb
    WHERE tb.LEV > 0
          AND EXISTS
    (
        SELECT 1 FROM #temp_bom AS tb2 WHERE tb2.PARENT_ITEM_ID = tb.ITEM_ID
    )
END



SELECT tb.ROOT_ITEM_ID,
       i.ITEM_CODE 根品号,
       --i.ITEM_NAME,
       tb.TOTAL_SEQ,
       tb.SEQ,
       tb.LEV,
       tb.PARENT_ITEM_ID,
       i2.ITEM_CODE 父品号,
       --i2.ITEM_NAME,
       tb.ITEM_ID,
       i3.ITEM_CODE 元件品号,
       --i3.ITEM_NAME,
       tb.EFFECTIVE_DATE,
       tb.EXPRITY_DATE,
       tb.QTY_PER
FROM #temp_bom AS tb
    LEFT JOIN dbo.ITEM AS i
        ON i.ITEM_BUSINESS_ID = tb.ROOT_ITEM_ID
    LEFT JOIN dbo.ITEM AS i2
        ON i2.ITEM_BUSINESS_ID = tb.PARENT_ITEM_ID
    LEFT JOIN dbo.ITEM AS i3
        ON i3.ITEM_BUSINESS_ID = tb.ITEM_ID
ORDER BY tb.TOTAL_SEQ