sql - 在 SQL Server 存储过程中显示父行下的子行

Question

有一个表，其中存储了有关项目的信息。每个项目都有一个 ID 列 (FPNNumber) 和一个父 ID 列。如果项目有父项目，则用户选择父项目的 ID 号以进入该行的父项目单元格。如果它没有父项目，则父项目列将有一个 0。我在这里看到了一些类似于我的问题，但看起来它们都在使用我没有或不想使用的级别列在我的查询中使用。

我从数据库中获取数据的存储如下所示:

SELECT h.[Priority]
  ,h.[FPNNumber]
  ,h.[ProjectName]
  ,sp.SponsorName
  ,pm.ProjectManagerName
  ,o.[OrgName]
  ,HealthID
  ,[StrategicAlignmentID] + [FinancialAlignmentID] + [TechnologyAlignmentID] As ProjectScore
  ,h.[Cost]
  ,h.[Budget]
  ,h.[PercentComplete]
  ,Convert(varchar(10),h.[EstimatedImpDt], 101) As EstimatedImpDt
  ,[EstimatedROI]
  ,h.[ExpectedBenefit]
  ,CONVERT(Decimal(10, 2),((DATEDIFF(day, h.[PaybackPerioddt], GETDATE()))/365.0)) As PaybackPerioddt

FROM [ProjectNew].[Header] h
   join ProjectNew.Sponsor sp ON h.SponsorID = sp.SponsorID
   join ProjectNew.ProjectManager pm ON h.ProjectManagerID = pm.ProjectManagerID
   Join ProjectNew.Organization o ON h.OrgID = o.OrgID
   where StatusID is null or StatusID < 12

有一件事我已经尝试过并部分工作但没有完全按照我的需要工作

;WITH Parents
AS(
   SELECT h.[Priority]
  ,h.[FPNNumber]
  ,h.[ProjectName]
  ,sp.SponsorName
  ,pm.ProjectManagerName
  ,o.[OrgName]
  ,HealthID
  ,[StrategicAlignmentID] + [FinancialAlignmentID] + [TechnologyAlignmentID] As ProjectScore
  ,h.[Cost]
  ,h.[Budget]
  ,h.[PercentComplete]
  ,Convert(varchar(10),h.[EstimatedImpDt], 101) As EstimatedImpDt
  ,[EstimatedROI]
  ,h.[ExpectedBenefit]
  ,CONVERT(Decimal(10, 2),((DATEDIFF(day, h.[PaybackPerioddt], GETDATE()))/365.0)) As PaybackPerioddt

FROM [FPN].[ProjectNew].[Header] h
  join ProjectNew.Sponsor sp ON h.SponsorID = sp.SponsorID
  join ProjectNew.ProjectManager pm ON h.ProjectManagerID = pm.ProjectManagerID
  Join ProjectNew.Organization o ON h.OrgID = o.OrgID
  WHERE StatusID is null or StatusID < 12 and h.ParentID = 0
Union All
  SELECT he.[Priority]
  ,he.[FPNNumber]
  ,he.[ProjectName]
  ,sp.SponsorName
  ,pm.ProjectManagerName
  ,o.[OrgName]
  ,he.HealthID
  ,[StrategicAlignmentID] + [FinancialAlignmentID] + [TechnologyAlignmentID] As ProjectScore
  ,he.[Cost]
  ,he.[Budget]
  ,he.[PercentComplete]
  ,Convert(varchar(10),he.[EstimatedImpDt], 101) As EstimatedImpDt
  ,he.[EstimatedROI]
  ,he.[ExpectedBenefit]
  ,CONVERT(Decimal(10, 2),((DATEDIFF(day, he.[PaybackPerioddt], GETDATE()))/365.0)) As PaybackPerioddt

FROM [FPN].[ProjectNew].[Header] he
  join ProjectNew.Sponsor sp ON he.SponsorID = sp.SponsorID
  join ProjectNew.ProjectManager pm ON he.ProjectManagerID = pm.ProjectManagerID
  Join ProjectNew.Organization o ON he.OrgID = o.OrgID
  Join Parents cte on cte.FPNNumber = he.ParentID
WHERE StatusID is null or StatusID < 12
)
Select * from Parents

当我执行它时，结果如下所示:

 ID | ParentID
----------------
 1  | 0
 2  | 0
 4  | 2
 5  | 2
 3  | 1
 6  | 1
 7  | 1

我需要它看起来像这样:

 ID | ParentID
----------------
 1  | 0
 3  | 1
 6  | 1
 7  | 1
 2  | 0
 4  | 2
 5  | 2

所以对于ID为2的行，其下有两个对应的子项目，但ID为1的子项目在其下方。

我做错了什么？有没有办法让所有子项目显示在他们的父项目下？如果是这样，有没有办法做到这一点，然后当我将该数据调用到 Visual Studio 中的 ListView 时，如果用户单击排序按钮，它不会扰乱子项目？

Answer 1

Select * from Parents
ORDER BY
    CASE WHEN ParentId = 0 THEN Id ELSE ParentId END
    ,ID

有趣的是，这个问题似乎仍然受到一些关注，所以我将稍微扩展一下我的叙述，以便更清楚地说明递归期间发生了什么。

你的 anchor 表开始并抓取 2 行，它保存在内存中，然后将这 2 行中的每一行连接到它们的子行，然后在下一次迭代中，这些子行然后连接到它们的子行。所以它同时遍历所有谱系，而不是一次一个，这就是为什么你得到显示它的顺序。首先是 0 级，然后是 1、2 级等。

因此，如果您希望结果按特定顺序排列，例如先父后子，您必须告诉 sql 使用 order by 语句对它们进行排序。在您的特定情况下，由于您在递归期间识别和继续您的 ParentIds 的方式，您需要使 Parentless Parent (0) 等于其自身，然后按 ids 排序。这可以在如上所示的 case 表达式中完成。