BOM多级查询表值函数

USE [数据库]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
tabDIYTable12746组件BOM表，F111904上级物料编码，F111885子物料编码，F111891单层用量
组件BOM的子件明细第一行为组件编辑，用量为1，上级ID为空
-- 第一步：创建优化索引（假设字段为字符串类型，空值为''）
-- 主索引：针对递归连接优化
CREATE INDEX IX_BOM_Parent_Material ON tabDIYTable12746 (F111904)
INCLUDE (F111882, F111891)
WHERE F111904 <> '' AND F111882 <> '' AND F111891 > 0;

-- 针对锚点查询的过滤索引
CREATE INDEX IX_BOM_Root ON tabDIYTable12746 (F111904, F111891, F111882)
WHERE F111904 = '';

-- 针对物料查询的反向索引
CREATE INDEX IX_BOM_Material ON tabDIYTable12746 (F111882, F111891)
WHERE F111882 <> '' AND F111891 > 0;
*/
-- 第二步：优化函数代码
ALTER FUNCTION [dbo].[ZJBOM]()
RETURNS TABLE
AS
RETURN
(
WITH CTE AS (
-- 锚点部分：只查询有效的根节点
SELECT
F111882 AS TopLevelMaterial,
F111882 AS Material,
CAST('A' AS VARCHAR(MAX)) AS LevelCode,
F111904 AS ParentMaterial,
CAST(F111891 AS DECIMAL(18, 8)) AS BOMUsage,
CAST(F111891 AS DECIMAL(18, 8)) AS TotalUsage,
1 AS Level
FROM tabDIYTable12746
WHERE F111904 = ''
AND F111882 <> ''
AND F111891 > 0

    UNION ALL

    -- 递归部分：优化连接查询
    SELECT
        c.TopLevelMaterial,
        b.F111882 AS Material,
        -- 保持原有层级编码样式
        c.LevelCode + '.' + 
            RIGHT('000' + CAST(
                DENSE_RANK() OVER (PARTITION BY b.F111904 ORDER BY b.F111882) 
            AS VARCHAR(3)), 3) AS LevelCode,
        b.F111904 AS ParentMaterial,
        CAST(b.F111891 AS DECIMAL(18, 8)) AS BOMUsage,
        CAST(c.TotalUsage * b.F111891 AS DECIMAL(18, 8)) AS TotalUsage,
        c.Level + 1
    FROM tabDIYTable12746 b
    INNER JOIN CTE c 
        ON b.F111904 = c.Material
    WHERE c.Level < 20
      AND b.F111882 <> '' 
      AND b.F111891 > 0
)

-- 最终结果
SELECT 
    TopLevelMaterial,
    LevelCode,
    ParentMaterial,
    Material AS LeafMaterial,
    BOMUsage,
    TotalUsage
FROM CTE
WHERE Material <> ''

)
/*
SELECT
TopLevelMaterial as 顶级物料,
CAST(LevelCode AS VARCHAR(2000)) as 层级编码,
ParentMaterial as 上级物料编码,
LeafMaterial as 子物料编码,
BOMUsage as 单层用量,
TotalUsage as 总成用量
FROM [dbo].[ZJBOM]()

*/