根据特定记录查找所有父级

Question

我需要关于 SQL 代码的帮助。我有两个表，第一个表的名称是：

NameID   Name      
1          John          
2          Paul          
3          Jessica          
4          Nancy          
5          Sam          
6          Jane
7          Jimmy

第二个是家庭表。

 FamilyID   NameID    ChildID
    1          1           2
    2          1           3
    3          2           4
    4          3           5
    5          3           6
    6          5           7

表格"Family"中的字段"NameID"和"ChildID"与表格"Name"中的字段"NameID"相连。因此，如果把它放在树形结构中，就会像这样。

         John
         /  \
      Paul  Jessica
      /       /  \
   Nancy    Sam  Jane
            /
          Jimmy

我需要的是能够查找某个记录的“所有”父级的 SQL 代码。例如：

1. 我想知道来自Jane的所有父级，结果将是：Jessica，John

2. 我想知道来自Jimmy的所有父级，结果将是：Sam，Jessica，John

3. 我想知道来自Nancy的所有父级，结果将是：Paul，John

- antonius aron

---

请问您能否提供您尝试过的查询语句？ - zedfoxus

实际上我仍在使用CTE递归进行工作，但尚未完成。 - antonius aron

你是指所有的祖先吗？这就是示例#2的建议。我会使用CTE。 - Peter Wone

@JohnSaunders 微软 SQL Server 2008 R2 - antonius aron

真实世界的家谱比你的示例要复杂。想象一下，Jimmy的父母是Sam和Jane，Nancy和Jessica是Sam的父母。那么Jimmy有所有节点作为他的父母。 - Eric

Answer 1

这里有一个方法，使用递归CTE如下：

  DECLARE @pName VARCHAR(20)
  SET @pName = 'Jane'

  ;WITH  RecursiveFamilyCTE
          AS (
               SELECT
                ParentName.NAME,
                ParentName.NameID,
                f.ChildID
               FROM
                dbo.Family AS f
                JOIN NAME AS ChildName
                  ON f.ChildID = ChildName.NameID
                JOIN Name AS ParentName
                  ON f.NameID = ParentName.NameID
               WHERE
                 ChildName.NAME = @pName

               UNION ALL

               SELECT 
                ParentName.NAME,
                ParentName.NameID,
                f.ChildID
               FROM
                dbo.Family AS f
                JOIN NAME AS ChildName
                  ON f.ChildID = ChildName.NameID
                JOIN Name AS ParentName
                  ON f.NameID = ParentName.NameID
                JOIN RecursiveFamilyCTE
                  ON f.ChildID = RecursiveFamilyCTE.NameID
             )
    SELECT
      NAME
    FROM
      RecursiveFamilyCTE

- JohnS

---

我刚刚修改了代码，使用了之前被忽略的 @pName 参数。 - JohnS

Answer 2

递归查询是解决这个问题的方法。以下是一种灵活的查询，您可以在不使用参数的情况下运行它。

with familytree as (
  select childid, nameid 
  from family
  where nameid is not null

  union all

  select f.childid, t.nameid  
  from familytree t 
  inner join family f on t.childid = f.nameid
),
treedetails as (
  select
    p.nameid as parentid, p.name as parent, 
    c.nameid as childid, c.name as child
  from familytree a
  left join name p on a.nameid = p.nameid
  left join name c on a.childid = c.nameid
)
-- uncomment the query of your choice
-- select * from treedetails where child = 'Jimmy'
-- select * from treedetails where child = 'Jane' 
-- select * from treedetails where child = 'Nancy'
-- 
-- show me children and grandchildren of Jessica
-- select * from treedetails where parent = 'Jessica' 

示例：http://www.sqlfiddle.com/#!3/f7030/4

- zedfoxus