WITH CTE AS
(
SELECT ID,FactoryID,ProjectSN,ProjectItem,PkgCode,ParentPkgCode,PkgType,PkgQty,cast(PkgCode as nvarchar(max)) as TE,cast(PkgCode as nvarchar(max)) as TreePath,
0 as TreeLevel,ROW_NUMBER()over(order by getdate()) as OrderID
FROM dbo.NCC_PackageMstr
UNION ALL
SELECT PM.ID,PM.FactoryID,PM.ProjectSN,PM.ProjectItem,PM.PkgCode,PM.ParentPkgCode,PM.PkgType,PM.PkgQty+CTE.PkgQty,
cast(replicate(' ',(CTE.TreeLevel+1)*6)+'|_'+PM.PkgCode as nvarchar(MAX)) as TE,
cast(CTE.TreePath+'::'+PM.PkgCode as nvarchar(MAX)) as TreePath,CTE.TreeLevel+1 as TreeLevel,
CTE.OrderID*100+ROW_NUMBER()over(Order by GETDATE()) as OrderID
FROM dbo.NCC_PackageMstr PM
INNER JOIN CTE ON PM.ParentPkgCode=CTE.PkgCode
)
select CTE.*
from CTE
WHERE ProjectSN='XX1406052' AND TreePath LIKE '20ST01-Y-ST-0001%'
ORDER BY LTRIM(OrderID);
改语句的执行结果：
希望得到的执行结果：
目的就是统计下级节点的PkgQty给上级节点，希望诸位大神能指点一二
------解决方案--------------------
你再update一下CTE表的PkgQty,大致为：
upate cte
set PkgQty = (select count(1)-1 from cte temp where temp.orderID like orderID )
------解决方案--------------------
1楼不是写了吗？直接UPDATE
------解决方案--------------------
分步完成,先产生为临时表,然后统计.