SQL-如何在自引用表中获取给定值的顶级父项

Question

我有这个小问题。

该表基本上如下所示

学科

• SubjectId
• SubjectName
• ParentSubjectId

ParentSubjectId引用主题表本身。并且可以下降很多级别（没有特定数量的级别）

示例（仅出于示例目的，使用国家/地区）：

  1.Europe
  2.America
  1.1 France
  1.1 Italy
  2.1 USA
  2.2 Canada
  1.1.1 Paris
  1.2.1 Rome

等等..

SubjectID是GUID ParentSubjectID也是GUID。

样本概述：http :
//i.imgur.com/a2u2CfT.png

它甚至可以无限期降低水平（甚至可以降低到街道号​​的水平）

我的问题是：给定一个主题（无论深度如何）。我想获得该学科的顶级家长（Europe/America在这种情况下）

我怎样才能做到这一点 ？是否可以使用基本SQL查询？

请注意，我根本无法修改数据库（我正在从现有数据库中查询数据）

Answer 1

写为：

declare @Subject as varchar(max)
set @Subject = 'Rome'; -- set subject name here

WITH SubjectCTE AS
(
SELECT SubjectId , SubjectName , ParentSubjectId
FROM Subject
WHERE SubjectName = @Subject 
UNION ALL
SELECT C.SubjectId , C.SubjectName , C.ParentSubjectId
FROM SubjectCTE AS P
JOIN Subject AS C
ON P.ParentSubjectId = C.SubjectId
)
,SubjectCTE2 as 
(
SELECT SubjectId , SubjectName , ParentSubjectId, 
       Row_Number() over ( order by SubjectId asc) as rownum
FROM SubjectCTE
)
select SubjectName as RequiredParentName
from SubjectCTE2
where rownum =1

在这里查看演示。