查找表的每一行的顶级父级[SQL Server 2008] [英] Finding Top level parent of each row of a table [SQL Server 2008]

问题描述

我有以下两个表

表人

 名称
 1 A 
 2 B 
 3 C 
 4 D 
 5 E 
  

表关系层次

  ParentId CHildId 
 2 1 
 3 2 
 4 3 
  

这将形成一个树状结构

  D 
 | 
 C 
 | 
 B 
 | 
 A 
  

ParentId和ChildId是Person表的Id列的外键

我需要编写可以获取我的顶级父级，即每个人的根。

以下CTE可以为每个人执行此操作。我将它转换为一个函数，并为每行Person运行它。我在Person表中有大约3k行，大约需要10秒。任何人都可以建议一种可以少用的方法。问题是在CTE之后运行3k次的函数

  DECLARE @childID INT 
 SET @childID = 1  -  - 要搜索的字段
 
; WITH RCTE AS 
（
 SELECT *，1 AS Lvl FROM RelationHierarchy 
 WHERE ChildID = @childID 
 
 UNION ALL 
 
 SELECT rh。*，Lvl + 1 AS Lvl FROM dbo.RelationHierarchy rh 
 INNER JOIN RCTE rc ON rh.CHildId = rc.ParentId 
）
 SELECT TOP 1 id，Name 
 FROM RCTE r 
 inner JOIN dbo.Person p ON p.id = r.ParentId 
 ORDER BY lvl DESC 
   
 p> 
 
解决方案
 / 17676944 / found-a-top-level-parent-in-sql / 17677469>原始问题，但不介意，这里也是一个副本：
 ; WITH RCTE AS 
（
 SELECT ParentId，ChildId，1 AS Lvl FROM RelationHierarchy 
 
 UNION ALL 
 
 SELECT rh.ParentId，rc.ChildId，Lvl + 1 AS Lvl 
 FROM dbo.RelationHierarchy rh 
 INNER JOIN RCTE rc ON rh.ChildId = rc.ParentId 
）
，CTE_RN AS 
（
 SELECT *，ROW_NUMBER（）OVER（PARTITION BY r.ChildID ORDER BY r.Lvl DESC）RN 
来自RCTE r 
 
） 
 SELECT pc.Id AS ChildID，pc.Name AS ChildName，r.ParentId，pp.Name AS ParentName 
 FROM dbo.Person pc 
 LEFT JOIN CTE_RN r ON pc.id = r。 CHildId AND RN = 1 
 LEFT JOIN dbo.Person pp ON pp.id = r.ParentId 
  
   SQLFiddle DEMO   
 
 
 
请注意，微小的差别在于CTE的递归部分。现在每次从锚部分重写childID。 
 
 
 
  EDIT  - 版本2    / p> 
 
 
在找到第一个查询的性能问题后，这里是一个改进的版本。从上到下，而不是其他方式 - 消除在CTE中创建额外的行，应该在大量的递归上更快：
 ; WITH RCTE AS 
（
 SELECT ParentId，CHildId，1 AS Lvl FROM RelationHierarchy r1 
 WHERE NOT EXISTS（SELECT * FROM RelationHierarchy r2 WHERE r2.CHildId = r1。 ParentId）
 
 UNION ALL 
 
 SELECT rc.ParentId，rh.CHildId，Lvl + 1 AS Lvl 
 FROM dbo.RelationHierarchy rh 
 INNER JOIN RCTE rc ON rc.CHildId = rh.ParentId 
）
 SELECT pc.Id AS ChildID，pc.Name AS ChildName，r.ParentId，pp.Name AS ParentName 
 FROM dbo.Person pc 
 LEFT JOIN RCTE r ON pc.id = r.CHildId 
 LEFT JOIN dbo.Person pp ON pp.id = r.ParentId 
  
   SQLFiddle DEMO   
 
I have following two tables 

Table Person
Id   Name
   1    A
   2    B
   3    C
   4    D
   5    E
Table RelationHierarchy
ParentId   CHildId
   2         1
   3         2
   4         3
This will form a tree like structure
      D
      |
      C
      |
      B
      |
      A
ParentId and ChildId are foreign keys of Id column of Person Table

I need to write SQL that Can fetch me Top Level Parent i-e Root of Each Person. 

Following CTE can do this for Each. I converted that to a Function and ran it for each row of Person. I have got about 3k rows in Person table and it takes about 10 Secs to do that. Can anyone suggest a approach that can take less. The Problem is the function that runs following CTE runs 3k times 
DECLARE @childID INT 
SET @childID  = 1 --chield to search

;WITH RCTE AS
(
SELECT *, 1 AS Lvl FROM RelationHierarchy 
WHERE ChildID = @childID

UNION ALL

SELECT rh.*, Lvl+1 AS Lvl FROM dbo.RelationHierarchy rh
INNER JOIN RCTE rc ON rh.CHildId = rc.ParentId
 )
SELECT TOP 1 id, Name
FROM RCTE r
inner JOIN dbo.Person p ON p.id = r.ParentId
ORDER BY lvl DESC

解决方案
I have also updated the answer in the original question, but never-mind, here is a copy also:
;WITH RCTE AS
(
    SELECT  ParentId, ChildId, 1 AS Lvl FROM RelationHierarchy 

    UNION ALL

    SELECT rh.ParentId, rc.ChildId, Lvl+1 AS Lvl 
    FROM dbo.RelationHierarchy rh
    INNER JOIN RCTE rc ON rh.ChildId = rc.ParentId
)
,CTE_RN AS 
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY r.ChildID ORDER BY r.Lvl DESC) RN
    FROM RCTE r

)
SELECT pc.Id AS ChildID, pc.Name AS ChildName, r.ParentId, pp.Name AS ParentName
FROM dbo.Person pc 
LEFT JOIN CTE_RN r ON pc.id = r.CHildId AND  RN =1
LEFT JOIN dbo.Person pp ON pp.id = r.ParentId
SQLFiddle DEMO

Note that the slight difference is in recursive part of CTE. ChildID is now rewritten each time from the anchor part. Also addition is ROW_NUMBER() function (and new CTE) to get the top level for each child at the end.

EDIT - Version2

After finding a performance issues with first query, here is an improved version. Going from top-to-bottom, instead of other way around - eliminating creating of extra rows in CTE, should be much faster on high number of recursions:
;WITH RCTE AS
(
    SELECT  ParentId, CHildId, 1 AS Lvl FROM RelationHierarchy r1
    WHERE NOT EXISTS (SELECT * FROM RelationHierarchy r2 WHERE r2.CHildId = r1.ParentId)

    UNION ALL

    SELECT rc.ParentId, rh.CHildId, Lvl+1 AS Lvl 
    FROM dbo.RelationHierarchy rh
    INNER JOIN RCTE rc ON rc.CHildId = rh.ParentId
)
SELECT pc.Id AS ChildID, pc.Name AS ChildName, r.ParentId, pp.Name AS ParentName
FROM dbo.Person pc 
LEFT JOIN RCTE r ON pc.id = r.CHildId
LEFT JOIN dbo.Person pp ON pp.id = r.ParentId 
SQLFiddle DEMO

                        
这篇关于查找表的每一行的顶级父级[SQL Server 2008]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！