BOM多级库存累计查询语句

--BOM多级库存累计查询语句
WITH LevelData AS (
SELECT
F111684 AS 单号,
F111685 AS 深度,
F111686 AS 层级,
F111687 AS 产品编码,
F111688 AS 数量,
F112053 AS 总单号,
F111683 AS 序号,
F112054 AS 上级所在序号,
/ 提取父级层级/
CASE
WHEN F111686 = '0' THEN NULL
WHEN CHARINDEX('.', F111686) = 0 THEN '0'
ELSE LEFT(F111686, LEN(F111686) - CHARINDEX('.', REVERSE(F111686)))
END AS 父级层级,
F111686 AS 完整层级
FROM tabDIYTable12734
),
ParentInfo AS (
SELECT
child.单号,
child.深度,
child.层级,
child.产品编码,
child.数量,
child.总单号,
child.序号,
/ 修改这里：获取上级编码对应的序号作为上级所在序号 /
parent.序号 AS 上级所在序号,
/ 这里修改了/
child.完整层级,
parent.产品编码 AS 上级编码,
parent.数量 AS 上级数量,
parent.完整层级 AS 上级完整层级
FROM LevelData child
LEFT JOIN LevelData parent
ON child.单号 = parent.单号
AND child.父级层级 = parent.层级
),
GroupCalculation AS (
SELECT
pi.,
/ 改进的分组键：包含完整的来源信息/
CASE
WHEN pi.上级编码 IS NULL THEN 'ROOT'
ELSE pi.上级编码 + '|' +
CASE
WHEN CHARINDEX('.', pi.完整层级) = 0 THEN pi.完整层级
ELSE LEFT(pi.完整层级,
CASE
WHEN CHARINDEX('.', pi.完整层级, CHARINDEX('.', pi.完整层级) + 1) > 0
THEN CHARINDEX('.', pi.完整层级, CHARINDEX('.', pi.完整层级) + 1) - 1
ELSE LEN(pi.完整层级)
END
)
END
END AS 分组键,
/ 上级组序：从上级完整路径提取/
CAST(
CASE
WHEN pi.上级完整层级 IS NULL THEN NULL
WHEN CHARINDEX('.', pi.上级完整层级) = 0
THEN pi.上级完整层级
ELSE RIGHT(pi.上级完整层级, CHARINDEX('.', REVERSE(pi.上级完整层级)) - 1)
END
AS DECIMAL(10, 2)) AS 上级组序
FROM ParentInfo pi
)
SELECT
单号,
深度,
层级,
产品编码,
数量,
总单号,
序号,
/ 处理没有上级的情况，将NULL转为0 /
ISNULL(上级所在序号, 0) AS 上级所在序号,
/ 这里处理NULL值/
上级编码,
上级数量,
上级组序,
/ 组序：确保同一上级下的不同路径分配到不同组/
DENSE_RANK() OVER (PARTITION BY 单号, 上级编码 ORDER BY 分组键) AS 组序,
/ 组内序号*/
ROW_NUMBER() OVER (PARTITION BY 单号, 上级编码, 分组键 ORDER BY 层级, 产品编码) AS 组内序号,
分组键 AS 分组标识
FROM GroupCalculation