假设我们有如下表格"#res"
MatID Number MatName StoreName StoreID SParentID Qty
2CA241F6-F48C-4A37-B675-25134C7F84D4 1 Mat1 Store1 194AB76D-01AB-4351-BA6F-5BE0974628F7 00000000-0000-0000-0000-000000000000 12
2CA241F6-F48C-4A37-B675-25134C7F84D4 1 Mat1 Store2 3A34B4D1-5464-461A-9115-20446253FE99 194AB76D-01AB-4351-BA6F-5BE0974628F7 1
2CA241F6-F48C-4A37-B675-25134C7F84D4 1 Mat1 Store3 4594C677-3F55-4E27-8EC9-8209C86FC0CD 3A34B4D1-5464-461A-9115-20446253FE99 1
表包含一个mat "Mat1"在3个不同的商店1,2,3中,如SParentID列"Store Parent "所示,store3是父store2的子,而store2有父store1(层次结构):
Store1
|
Store2
|
Store3
我想将store3中的qty的值添加到store2中使其变为2,然后将2添加到父节点。请注意,这是一个例子,我想以一般的方式做到这一点,不管孩子的数量和最高的父母可能有不存在于记录集中的父母!
请检查这个递归SELECT查询。我将主表重命名为store。您可以在此引用链接
中使用CTE引用SQL递归查询结构。;with rcte as (
select StoreId, SParentId, MatID, ISNULL(Qty,0) Qty from store where SParentId in (select StoreId from store)
union all
select
store.StoreId, store.SParentId, store.MatID, ISNULL(store.Qty,0) + rcte.Qty as Qty
from store
inner join rcte
on rcte.SParentId = store.StoreId
)
select
*
from (
select rn = ROW_NUMBER() OVER (Partition By StoreId Order By Qty Desc), * from rcte
) t
inner join store s ON s.MatID = t.MatID and s.StoreId = t.StoreId
where t.rn = 1
我希望它能成功