SQL Server 2012 CTE查找分层数据的根目录或顶级父目录
我遇到了一个问题，试图递归地遍历层次结构，以找到可能具有多个顶级节点的组织结构中所有后代节点的顶层节点。 我试图使用SQL Server 2012 CTE来做到这一点，但它不会递归到达每个分支的顶级节点。 我已经尝试写下我的查询，如其他与此相关的帖子所示，但仍然没有骰子。 （至少我认为我是。）我希望有人能告诉我我在这里做错了什么？ 这篇文章与我正在尝试做的事情有很大关系，并且我遵循了接受的答案，但是我仍然没有“得到它”：在SQL中查找顶级父项
如上所示，我有OrgGroups引用直接父组，除非它是顶级，然后它是NULL。 例如，（4）财务（顶级） - >（5）人力资源 - >（11）福利
我想创建一个数据库视图，列出每个OrgGroup以及他们的TOP-MOST祖先的ID。 （不是他们的直接父母）
因此，例如，DB View将为（11）Benefits OrgGroup记录一个记录，并为其（4）Finance的最高parentgroupId提供相应的列值。
;WITH OrgStructureIndex AS
(
SELECT O.OrgGroupId, O.Name, O.OrgStructureId, O.ParentGroupId, 1 AS Lvl
FROM OrgGroups O
UNION ALL
SELECT OG.OrgGroupId, OG.Name, OG.OrgStructureId, OG.ParentGroupId, Lvl+1 AS Lvl
FROM OrgGroups OG INNER JOIN OrgStructureIndex OI
ON OI.OrgGroupId = OG.ParentGroupId
)
SELECT * FROM OrgStructureIndex
这导致福利组织组拥有（5）HR的最高ParentGroupId。 期望的结果将是（4）财务。 它也会导致重复记录。
至少要删除重复项，我已将SQL更改为：
;WITH OrgStructureIndex AS
(
SELECT O.OrgGroupId, O.Name, O.OrgStructureId, O.ParentGroupId, 1 AS Lvl
FROM OrgGroups O
UNION ALL
SELECT OG.OrgGroupId, OG.Name, OG.OrgStructureId, OG.ParentGroupId, Lvl+1 AS Lvl
FROM OrgGroups OG INNER JOIN OrgStructureIndex OI
ON OI.OrgGroupId = OG.ParentGroupId
)
,CTE_RN AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY oi.OrgGroupId ORDER BY oi.Lvl DESC) RN
FROM OrgStructureIndex oi
)
SELECT * FROM CTE_RN
WHERE RN = 1
我在哪里在这里短？ TIA
两个缺点：
Id 。 以下是您可以如何解决它们的方法：
;WITH OrgStructureIndex AS
(
SELECT O.OrgGroupId, O.Name, O.OrgStructureId, O.ParentGroupId, 1 AS Lvl,
-- #2
o.OrgGroupId as [RootGroupId]
FROM OrgGroups O
-- #1
where o.ParentGroupId is null
UNION ALL
SELECT OG.OrgGroupId, OG.Name, OG.OrgStructureId, OG.ParentGroupId, Lvl+1 AS Lvl,
-- #2
oi.RootGroupId
FROM OrgGroups OG INNER JOIN OrgStructureIndex OI
ON OI.OrgGroupId = OG.ParentGroupId
)
SELECT * FROM OrgStructureIndex;
您确实可以从叶节点向上走 - 正如您所做的那样 - 查找每个原始行的根。 你遗失的东西正在追踪起始叶子。 剥离示例：
小提琴
CREATE TABLE OrgGroup (OrgGroupId INT, Name VARCHAR(10), ParentGroupId INT)
GO
INSERT INTO OrgGroup VALUES
(1,'Main', NULL),
(2,'IT',1),
(3,'DotCom',2),
(4,'Finance', NULL),
(5,'HR',4),
(6,'Accounting',4)
GO
;WITH cte AS
(
SELECT 1 AS Lvl
,OrgGroupId LeafId
,OrgGroupId
,ParentGroupId
,Name
,Name LeafName
FROM OrgGroup
UNION ALL
SELECT Lvl+1 AS Lvl
,OI.LeafId
,OG.OrgGroupId
,OG.ParentGroupId
,OG.Name
,OI.LeafName
FROM OrgGroup OG
INNER JOIN
cte OI ON OI.ParentGroupId = OG.OrgGroupId
)
,cte_rn AS (
SELECT *
,ROW_NUMBER() OVER (PARTITION BY LeafID ORDER BY Lvl DESC) rn
FROM cte
)
SELECT * FROM cte_rn WHERE rn = 1*
上一篇: SQL Server 2012 CTE Find Root or Top Parent of Hierarchical Data