logo标签列表

在SQL Server存储过程中显示子行在父行下方

sqlsql-serverssms
3
有一个表格存储了项目相关的信息。每个项目都有一个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中的数据时，使用户单击排序按钮时不会打乱子项目的顺序？

请展示预期数据。 - TheGameiswar
仅查看您的结果而非代码，似乎您只需要在递归CTE的选择中添加一个ORDER BY语句。这是一个典型的结果，但您需要在之后进行排序。 - Matt
1
@TheGameiswar 我已经添加了它。 - Thomas Colbert
3个回答
6
Select * from Parents
ORDER BY
    CASE WHEN ParentId = 0 THEN Id ELSE ParentId END
    ,ID

有趣的是，这个问题似乎仍然受到一些关注，因此我会扩展一下我的叙述，以使递归期间发生的事情更加清晰。
你的锚定表开始并获取了2行，它将这些行保存在内存中，然后将这两个行与它们的子行连接起来，然后在下一次迭代中，这些子行再连接到它们的子行。因此，它同时遍历所有的血统，而不是一次一个，这就是为什么你会得到显示顺序。先是0级，然后是1、2等级。
因此，如果你希望按照特定的顺序获得结果，例如父项然后子项，你必须告诉sql用order by语句对它们进行排序。在你的特定情况下，由于你在递归过程中标识并继续使用ParentIds的方式，你需要将无父项的父项（0）设置为自身，然后按id排序。这可以在上面显示的case表达式中完成。
0
如果我理解你的问题正确，那么你似乎想确保层次结构是正确的顺序。
以下是一个通用示例，使用基于标题的序列，但可以是任何可用字段。
此外，范围键是完全可选的。（如果不需要，则删除cteR1和cteR2）
Declare @Table table (ID int,Pt int,Title varchar(50))
Insert into @Table values (0,null,'Tags'),(1,0,'Transportation'),(2,1,'Boats'),(3,1,'Cars'),(4,1,'Planes'),(5,1,'Trains'),(6,0,'Technology'),(7,6,'FTP'),(8,6,'HTTP'),(9,0,'Finance'),(10,9,'FTP'),(11,9,'401K'),(12,2,'Sail'),(13,2,'Powered'),(14,6,'Internet'),(15,6,'Database'),(16,15,'SQL Server'),(17,15,'MySQL'),(18,15,'MS Access')

Declare @Top  int = null             --<<  Sets top of Hier Try 9
Declare @Nest varchar(25) ='|-----'  --<<  Optional: Added for readability

;with cteHB as (
    Select  Seq  = cast(1000+Row_Number() over (Order by Title) as varchar(500))
           ,ID
           ,Pt
           ,Lvl=1
           ,Title 
     From   @Table 
     Where  IsNull(@Top,-1) = case when @Top is null then isnull(Pt,-1) else ID end
     Union  All
     Select Seq  = cast(concat(cteHB.Seq,'.',1000+Row_Number() over (Order by cteCD.Title)) as varchar(500))
           ,cteCD.ID
           ,cteCD.Pt,cteHB.Lvl+1
           ,cteCD.Title 
     From   @Table cteCD 
     Join   cteHB on cteCD.Pt = cteHB.ID)
    ,cteR1 as (Select Seq,ID,R1=Row_Number() over (Order By Seq) From cteHB)
    ,cteR2 as (Select A.Seq,A.ID,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.ID )
Select B.R1  
      ,C.R2
      ,A.ID
      ,A.Pt
      ,A.Lvl
      ,Title = Replicate(@Nest,A.Lvl-1) + A.Title
      ,A.Seq   -- < Included for Illustration
 From cteHB A
 Join cteR1 B on A.ID=B.ID
 Join cteR2 C on A.ID=C.ID
 Order By B.R1

返回

enter image description here

-1
您可以尝试类似于下面的stackoverflow问题提供的解决方案： Query to get parent records with child record, followed by next parent-child records in mysql 或者您可以尝试以下解决方案。
我已经提供了我的解决方案，以获得您想要的顺序。我使用了rnk作为附加列，您可以在最终结果集中忽略它。
CREATE TABLE #ParentChild (
    projectId INT
    ,parentProjectId INT
    )

INSERT INTO #ParentChild
VALUES (1,0),(2,0),(3,1),(4,1),(5,1),(6,2),(7,2),(8,2);

SELECT projectid
    ,parentprojectid
    ,row_number() OVER (
        PARTITION BY parentconsider ORDER BY projectid
        ) AS rnk
FROM (
    SELECT projectId
        ,parentProjectId
        ,CASE 
            WHEN parentProjectId = 0
                THEN projectId
            ELSE parentProjectId
            END AS parentconsider
    FROM #ParentChild
    ) AS x
增加了我的实现以提高清晰度。 - Venkataraman R
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接