-- 优化后的实时ZJBOM2函数
/
源表：tabDIYTable12746
子件ID：F111882
子件用量：F111891
上级ID：F111904
select TopLevelMaterial AS 顶级物料,LevelCode AS 层级编码,ParentMaterial AS 上级物料编码,LeafMaterial as 子物料编码,BOMUsage AS 单层用量,TotalUsage AS 总成用量, SortCode AS 层级编码2
FROM [dbo].[ZJBOM2]()
/
CREATE OR ALTER FUNCTION [dbo].[ZJBOM2]()
RETURNS TABLE
AS
RETURN
(
WITH BOM_CTE AS (
-- 锚点成员：顶级物料
-- 使用索引提示优化
SELECT
F111882 AS TopLevelMaterial,
F111882 AS Material,
CAST('1' AS VARCHAR(MAX)) AS LevelCode,
CAST('001' AS VARCHAR(MAX)) AS SortCode,
F111904 AS ParentMaterial,
CAST(F111891 AS DECIMAL(18, 8)) AS BOMUsage,
CAST(F111891 AS DECIMAL(18, 8)) AS TotalUsage,
1 AS Level
FROM dbo.tabDIYTable12746
WHERE ISNULL(F111904, '') = ''
AND ISNULL(F111882, '') <> ''
AND ISNULL(F111891, 0) > 0

    UNION ALL

    -- 递归成员：子物料
    -- 优化JOIN条件，使用索引
    SELECT
        c.TopLevelMaterial,
        b.F111882,
        -- 优化：避免字符串操作性能问题
        CASE WHEN c.Level < 15 
             THEN CAST(c.LevelCode + '.' + 
                    CAST(ROW_NUMBER() OVER (
                        PARTITION BY b.F111904 
                        ORDER BY b.F111882
                    ) AS VARCHAR(10)) AS VARCHAR(MAX))
             ELSE c.LevelCode + '.x'
        END AS LevelCode,
        -- 优化：减少字符串拼接
        CASE WHEN c.Level < 15
             THEN CAST(c.SortCode + '.' + 
                    RIGHT('000' + CAST(
                        ROW_NUMBER() OVER (
                            PARTITION BY b.F111904 
                            ORDER BY b.F111882
                        ) AS VARCHAR(3)
                    ), 3) AS VARCHAR(MAX))
             ELSE c.SortCode + '.999'
        END AS SortCode,
        b.F111904,
        CAST(b.F111891 AS DECIMAL(18, 8)),
        CAST(CASE WHEN c.Level < 15 
                  THEN ROUND(c.TotalUsage * b.F111891, 8)
                  ELSE 0
             END AS DECIMAL(18, 8)),
        c.Level + 1
    FROM dbo.tabDIYTable12746 b
    -- 使用INNER JOIN，强制使用索引
    INNER JOIN BOM_CTE c 
        ON b.F111904 = c.Material
        AND c.Level < 15  -- 提前终止递归
    WHERE ISNULL(b.F111882, '') <> '' 
      AND ISNULL(b.F111891, 0) > 0
)

SELECT 
    TopLevelMaterial,
    LevelCode,
    SortCode,
    ParentMaterial,
    Material AS LeafMaterial,
    BOMUsage,
    TotalUsage,
    Level
FROM BOM_CTE
WHERE ISNULL(Material, '') <> ''
  AND Level <= 15

)

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。