以前的代码我都看了,当有一层BOM中的数量大于1时显示的出来的总数量就是错误的。
比如下面代码:
WITH ItemTree(itemcode,lvl, Quantity)
AS
(
SELECT itemcode, 1, convert(numeric(19, 6),1) quantity
FROM oitm
WHERE itemcode in (SELECT T1.[ItemCode] FROM ORDR T0INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocNum] ='[%0]')
UNION ALL
SELECT E.Code, M.lvl+1, E.quantity
FROM itt1 AS E
JOIN ItemTree AS M
ON E.father = M.itemcode
)
SELECT T0.ITEMCODE,CONVERT(VARCHAR(255),sum(T0.QUANTITY)) 数量FROM ItemTreeT0
where lvl > 1 and itemcode not in (select father from itt1)
group by T0.ITEMCODE |