K3bom多级展开

-- 创建并初始化临时表 #bom 和 #bomchild
CREATE TABLE #bom
(
    FId int IDENTITY(1,1),
    FItemID int,
    FNumber nvarchar(200)
);

CREATE TABLE #bomchild
(
    FID int IDENTITY(1,1),
    FParentID int,
    FLevel int,
    FItemID int,
    FBOMInterID int,
    FEntryID int
);

-- 插入数据到 #bom 表
INSERT INTO #bom (FItemID, FNumber)
SELECT t.Fitemid, t.FNumber 
FROM t_ICItem t 
INNER JOIN t_item t5 ON t5.FItemID = t.Fitemid
WHERE t.FErpClsID IN (1, 2, 3, 5) 
AND t5.FItemClassID = 4 
AND t5.FDeleted = 0 
AND t5.FDetail = 1 
ORDER BY t.fnumber;

-- 使用递归CTE处理BOM层级
WITH RecursiveBOM AS (
    SELECT 
        u1.FId AS FParentID,
        0 AS FLevel,
        u1.FItemID,
        COALESCE(t1.FInterID, -1) AS FBOMInterID,
        -1 AS FEntryID
    FROM 
        #bom u1
    LEFT JOIN 
        ICBOM t1 ON u1.FItemID = t1.FItemID AND t1.FUseStatus = 1072

    UNION ALL

    SELECT 
        rb.FParentID,
        rb.FLevel + 1,
        t2.Fitemid,
        t2.FInterID,
        t2.FEntryID
    FROM 
        RecursiveBOM rb
    INNER JOIN 
        icbom t1 ON rb.FItemID = t1.FItemID
    INNER JOIN 
        ICBOMChild t2 ON t2.FInterID = t1.FInterID AND t1.FUseStatus = 1072
    WHERE 
        rb.FLevel < 19 -- 设置最大层级限制
)

-- 将递归CTE的结果插入到 #bomchild 表
INSERT INTO #bomchild
    (FParentID, FLevel, FItemID, FBOMInterID, FEntryID)
SELECT 
    FParentID, FLevel, FItemID, FBOMInterID, FEntryID
FROM 
    RecursiveBOM;

-- 最终查询展示结果
SELECT
    t2.FModel 专机号,
    t4.FBOMNumber BOM编号,
    t2.FNumber 产品代码,
    t2.FName 产品名称,
    t1.FLevel 层级,
    t3.FNumber 材料代码,
    t3.FName 材料名称,
    t3.FModel 材料规格,
    -- 注意: FQty 不再从 #bomchild 获取，可能需要从其他表或计算得出
    -- t1.FQty 产品用量,
    yy.fname 材料属性,
    t5.FQty 单位用量,
    t5.FScrap 损耗率,
    t6.FName AS 是否跳层,
    t5.FNote 备注,
    t5.FPositionNo 位置号
FROM 
    #bom u1
LEFT JOIN 
    #bomchild t1 ON u1.FId = t1.FParentID
LEFT JOIN 
    t_icitem t2 ON t2.FItemID = u1.FItemID
LEFT JOIN 
    t_ICItem t3 ON t3.FItemID = t1.FItemID
LEFT JOIN 
    ICBOM t4 ON t4.FInterID = t1.FBOMInterID
LEFT JOIN 
    ICBOMChild t5 ON t5.FInterID = t1.FBOMInterID AND t5.FEntryID = t1.FEntryID
LEFT JOIN 
    t_SubMessage t6 ON t6.FInterID = t4.FBOMSkip
LEFT JOIN 
    t_SubMessage yy ON yy.FInterID = t3.FErpClsID
    AND t4.FUseStatus = 1072
    AND t4.FBomType <> 3 --FBomType；-普通,1-配置类,2-生产规划类,3-客户BOM,4-特征类
ORDER BY 
    u1.FId, t1.FLevel;

-- 清理临时表
DROP TABLE #bom;
DROP TABLE #bomchild