跳至主要內容

条件案例:获取BOM所有层级结构

五六零网校小于 1 分钟

示例代码

WITH Bom (Father, code, BomLevel, sort, warehouse, Qty, issue, txt) AS (
		SELECT T0.father, T0.code, 1 AS bomlevel
			, CONVERT(VARCHAR(255), T0.father + CHAR(9) + CAST(T0.ChildNum + 10 AS NVARCHAR))
			, T0.warehouse, CAST(T0.quantity / T1.QAuNTITY AS NUMERIC(19, 6)) AS quantity, T0.issuemthd, T0.LineText
		FROM ITT1 T0
			INNER JOIN OITT T1 ON T1.code = T0.father
		WHERE T0.father NOT IN (
				SELECT code
				FROM ITT1
				WHERE code IS NOT NULL
			)
		UNION ALL
		SELECT T0.father, T0.code, bomlevel + 1
			, CONVERT(VARCHAR(255), RTRIM(sort) + CAST(T0.ChildNum + 10 AS NVARCHAR) + CHAR(9))
			, T0.warehouse, CAST(T0.quantity / T1.Qauntity * T2.Qty AS NUMERIC(19, 6)) AS quantity, T0.issuemthd, T0.LineText
		FROM ITT1 T0
			INNER JOIN OITT T1 ON T0.FATHER = T1.CODE
			INNER JOIN Bom T2 ON T0.father = T2.code
	)


SELECT * FROM BOM
ORDER BY sort