SQL - 如何在自引用表中获取给定值的顶级父级 [英] SQL - how to get the top parent of a given value in a self referencing table

问题描述

我遇到了这个小问题.

表格基本上如下

主题

• 主题 ID
• 主题名称
• ParentSubjectId

ParentSubjectId 引用主题表本身.它可以下降很多级别(没有具体的级别数)

例子(使用国家只是为了这个例子):

 1.欧洲2.美国1.1 法国1.1 意大利2.1 美国2.2 加拿大1.1.1 巴黎1.2.1 罗马

等等..

SubjectID 是 GuidParentSubjectID 也是一个 GUID.

示例概览:http://i.imgur.com/a2u2CfT.png

它甚至可以无限期地继续下降(甚至可能下降到街道号码级别)

我的问题是:给定一个主题(无论深度如何).我想获得该主题的最高父级(在这种情况下为 Europe/America)

我该怎么做?是否可以使用基本 SQL 查询?

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

解决方案

Write as:

 声明 @Subject 为 varchar(max)设置@Subject = '罗马';-- 在此处设置主题名称使用主题CTE AS(选择 SubjectId 、 SubjectName 、 ParentSubjectId发件人WHERE 主题名称 = @Subject联合所有选择 C.SubjectId , C.SubjectName , C.ParentSubjectId来自 SubjectCTE AS P加入主题为 CON P.ParentSubjectId = C.SubjectId),SubjectCTE2 为(选择 SubjectId , SubjectName , ParentSubjectId,Row_Number() over ( order by SubjectId asc) as rownum来自主题CTE)选择 SubjectName 作为 RequiredParentName来自 SubjectCTE2其中rownum = 1

在此处查看演示..

I'm having this small issue.

the table basically looks like the following

Subject

• SubjectId
• SubjectName
• ParentSubjectId

ParentSubjectId references the subject table itself. and it can go down for many levels (no specific number of levels)

example (using countries just for the sake of this example):

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

and so on..

SubjectID is Guid ParentSubjectID is a GUID too.

Sample Overview: http://i.imgur.com/a2u2CfT.png

it can even keep going down in levels indefinitely (maybe even to the street number level)

my question is: given a subject (no matter the depth). i would like to get the top parent of that subject (Europe/America in this case)

How can i do this ? Is it possible using Basic SQL query ?

please note that i cannot modify the database at all (i'm querying data from an already existing database)

解决方案

Write as:

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 

check demo here..

这篇关于SQL - 如何在自引用表中获取给定值的顶级父级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！