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

写为：

declare @Subject as varchar(max)set @Subject = 'Rome'; -- set subject name hereWITH SubjectCTE AS(SELECt SubjectId , SubjectName , ParentSubjectIdFROM SubjectWHERe SubjectName = @Subject UNIOn ALLSELECt C.SubjectId , C.SubjectName , C.ParentSubjectIdFROM SubjectCTE AS PJOIN Subject AS CON P.ParentSubjectId = C.SubjectId),SubjectCTE2 as (SELECt SubjectId , SubjectName , ParentSubjectId,        Row_Number() over ( order by SubjectId asc) as rownumFROM SubjectCTE)select SubjectName as RequiredParentNamefrom SubjectCTE2where rownum =1

在这里查看演示。