如何在MS SQL Server中的查询方案中加入

Question

我有 2 个表，分别是 1.#records 和 2.ItemRelation

1.#records

ID      ItemLookupCode    Qty   ParentItem  StoreID
---------------------------------------------------
70707   130679C0400       -1    0            1004    --parent
78910   130679T0020       19    70707        1004    --child1
70706   130679T0010        1    78910        1004    --child2
45913   130679P0001        9    70706        1004    --child3
70707   130679C0400        1    0            1001    --Parent
78910   130679T0020        0    70707        1001    --child1
70706   130679T0010        0    78910        1001    --child2
                                                     --no child3

2.项目关系

 ID   ItemLookupCode  ChildID1  ChildParentItem1 ChildParentQty1 ChildID2 ChildParentQty2 ChildID3 ChildParentQty3 LastChildParentQty
70707 130679C0400     78910     70707            20               70706   2               45913           10                  400

期待结果

期望结果与ItemRelation表相同。但是在各自的子子项上添加数量和存储ID

更新

ItemRelation = ir
#records = rec
rec.StoreID | ir.ID | rec.Qty as ParentQty | ir.ChildID1 | rec.Qty as ChildQty1 | ir.ChildID2 | rec.Qty as ChildQty2 | ir.ChildID3 | rec.Qty as ChildQty3 | ir.lastchildQty
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1004          70707    -1                     78910          19                      70706              1                  45913            9                 400
1001          70707     1                     78910          0                       70706              0                                                     400

Answer 1

使用 INNER JOIN ：

SELECT rec.StoreID,
       ir.ID,
       rec.Qty as ParentQty,
       ir.ChildID1,
       rec.Qty as ChildQty1,
       ir.ChildID2,
       rec.Qty as ChildQty2,
       ir.ChildID3 ,
       rec.Qty as ChildQty3,
       ir.lastchildQty
FROM ItemRelation Ir INNER JOIN #records Rec
ON Ir.ID  = Rec.ID;

Answer 2

select parentRec.StoreID, ir.ID, parentRec.Qty as ParentQty,
       childRec1.ID as ChildID1, childRec1.Qty as ChildQty1, 
       childRec2.ID as ChildID2, childRec2.Qty as ChildQty2, 
       childRec3.ID as ChildID3, childRec3.Qty as ChildQty3
  from ItemRelation ir
  join #records parentRec on parentRec.Id = ir.id
  join #records childRec1 on childRec1.ParentItem = parentRec.ID
  join #records childRec2 on childRec2.ParentItem = childRec1.ID
  join #records childRec3 on childRec3.ParentItem = childRec2.ID

Answer 3

终于我得到了我的结果

SELECT RecParent.StoreID,
   ir.ID,
   RecParent.Qty as ParentQty,
   ir.ChildID1,
   RecChild1.Qty as ChildQty1,
   ir.ChildID2,
   RecChild2.Qty as ChildQty2,
   ir.ChildID3 ,
   RecChild3.Qty as ChildQty3,
   ir.LastChildParentQty
FROM ItemRelation Ir 
    INNER JOIN #records RecParent ON Ir.ID  = RecParent.ID and RecParent.ParentItem = 0
    Left JOIN #records RecChild1 ON Ir.ChildID1  = RecChild1.ID and ir.ID = RecChild1.ParentItem and RecParent.StoreID = RecChild1.StoreID
    Left JOIN #records RecChild2 ON Ir.ChildID2  = RecChild2.ID and ir.ChildID1 = RecChild2.ParentItem and RecParent.StoreID = RecChild2.StoreID
    Left JOIN #records RecChild3 ON Ir.ChildID3  = RecChild3.ID and ir.ChildID2 = RecChild3.ParentItem and RecParent.StoreID = RecChild3.StoreID

它带来了确切的结果。

谢谢大家