应该没有 自动增长列,我自己+了个 identity(int,1,1)
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
存放到 虚拟表#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 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
------解决思路----------------------
;WITH t AS
(
-- 这已经是棵树了
)
SELECT a.*,lvl
FROM #TempBom a
JOIN t b
ON a.ID=b.ID --要输出结果应该是用ID关联啊
ORDER BY b.px