34,590
社区成员
发帖
与我相关
我的任务
分享
SELECT identity(int,1,1) as ID, [Flag]
,[ProductID]
,[SubProdID]
,[QtyOfBatch]
,[BatchAmount] into #TempBom
FROM [CHIComp88].[dbo].[prdBOMMats] where ProductID='HCFWC11A.00000000000'
select * from #TempBom
;WITH t AS
(
SELECT ID,lvl=0,px=CAST(ID AS VARBINARY),ProductID, SubProdID
FROM #TempBom t
--WHERE ProductID='HCFWC11A.00000000000'
UNION ALL
SELECT a.ID,lvl+1,CAST(px+CAST(a.ID AS VARBINARY) AS VARBINARY),a.ProductID, b.SubProdID
FROM #TempBom a
JOIN t b
ON a.ProductID = b.SubProdID
)
SELECT a.*,lvl
FROM #TempBom a
JOIN t b
ON a.ProductID=b.SubProdID
ORDER BY b.px
drop table #TempBom
;WITH t AS
(
SELECT ID,lvl=0,px=CAST(ID AS VARBINARY),ProductID, SubProdID
FROM #TempBom t
--WHERE ProductID='HCFWC11A.00000000000'
UNION ALL
SELECT a.ID,lvl+1,CAST(px+CAST(a.ID AS VARBINARY) AS VARBINARY),
a.ProductID,
b.SubProdID --等于再输出一个 a.ProductID 算什么?应该是 a.SubProdID
FROM #TempBom a
JOIN t b
ON a.ProductID = b.SubProdID --它们是相等的
)
SELECT * FROM t ORDER BY px
SELECT a.*,t.lvl
FROM t
JOIN #TempBom a ON a.ID = t.ID
ORDER BY t.px
;WITH t AS
(
SELECT ID,lvl=0,px=CAST(ID AS VARBINARY),ProductID, SubProdID
FROM #TempBom t
WHERE NOT EXISTS(SELECT 1 FROM #TempBom WHERE SubProdID=t.ProductID)
UNION ALL
SELECT a.ID,lvl+1,CAST(px+CAST(a.ID AS VARBINARY) AS VARBINARY),a.ProductID, b.SubProdID
FROM #TempBom a
JOIN t b
ON a.SubProdID = b.ProductID
)SELECT * FROM t
参照版主,这样有数据吗;WITH t AS
(
-- 这已经是棵树了
)
SELECT a.*,lvl
FROM #TempBom a
JOIN t b
ON a.ID=b.ID --要输出结果应该是用ID关联啊
ORDER BY b.px
;WITH t AS
(
SELECT ID,lvl=0,px=CAST(ID AS VARBINARY),ProductID, SubProdID
FROM #TempBom t
WHERE NOT EXISTS(SELECT 1 FROM #TempBom WHERE SubProdID=t.ProductID)
UNION ALL
SELECT a.ID,lvl+1,CAST(px+CAST(a.ID AS VARBINARY) AS VARBINARY),a.ProductID, b.SubProdID
FROM #TempBom a
JOIN t b
ON a.SubProdID = b.ProductID
)
SELECT a.*,lvl
FROM #TempBom a
JOIN t b
ON a.ProductID=b.SubProdID
ORDER BY b.px
drop table #TempBom