分层产品结构

Question

我正在研究一个在pic basic（yippee）中的报告，并将其转换为.net。该报告最终在下面看。该表（仅显示一部分数据以使其简单）具有parentID（ID）和ChildID，并基本上加入直到没有ChildID可用（null）。我编写了一个我认为可以运行良好的过程，但是坐着并且不会返回任何类型的结果。SQL不是我的强项。

报告

0.........  SA33028
 1........  42S0131
 1........  5S0127
 1........  6S0175
 1........  SA33023
  2.......  16S0142
  2.......  18S0103
  2.......  24S0108
  2.......  28SM0105
  2.......  S30461.AI
  2.......  S30463
   3......  S28807
   3......  S28807
  2.......  S30641
  2.......  S31134
  2.......  S9383
  2.......  SA29801
   3......  16S0116
   3......  S27798
   3......  S31170
   3......  SA30059
    4.....  38S0116
    4.....  7S0105
    4.....  S19430.145
     5....  7SM0145
      6...  7SM0145.001
      6...  7SM0145.002
     5....  S19430
    4.....  S19431
  2.......  SA30910

表：

ParentID      ChildID
SA33028   42S0131
SA33028   5S0127
SA33028   6S0175
SA33028   79S0101
SA33028   S11870
SA33028   S30151
SA33028   SA33023
SA33028   SA33029
SA33023   S30463
S30463    S28807

SQL过程：

ALTER PROCEDURE [dbo].[getHierarchy]
@Parent VARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;
WITH AllParts([PSNbr], [ChildID], [ParentID])
AS 
(
 SELECT Child.Parent_Part AS PSNbr, 
 Child.Parent_Part AS ChildID, 
 Parent.Parent_Part AS ParentID
 FROM ps AS Child
 LEFT OUTER JOIN ps AS Parent
 ON Child.Component_Part = Parent.Parent_Part
 UNION ALL
 SELECT AllParts.PSNbr AS PSNbr,
    AllParts.ParentID AS ChildID, 
    NewParent.Component_Part AS ParentID
 FROM AllParts
 INNER JOIN PS AS NewParent
 ON AllParts.ParentID = NewParent.Parent_Part
    AND AllParts.ParentID <> AllParts.ChildID
 INNER JOIN PS as NewParentInfo
 ON NewParent.Component_Part = NewParentInfo.Parent_Part
)
SELECT AllParts.[PSNbr],
       AllParts.[ParentID]
FROM AllParts
WHERE PSNbr = @Parent;

Answer 1

为了纪念亲吻原则，我要问。您如何测试该过程？您只是击中F5还是从SQL内运行？尝试从"开始"到您的"结束"，然后击中F5。（顺便说一句，您在上面的示例中没有"结束"）