家谱sql查询(family tree sql query)

我工作的家谱应用在asp.net与SQL Server 2008。

我的主表是这样的： -

ID int PK
Name Varchar(50)
MotherID int
FatherID int

ID  MotherID FatherID   Name

1   NULL    NULL    My Grand Father

2   NULL    NULL    My Grand Mother

3   someid  someid  My Mother

4   2   1   My Father

5   3   4   Me

6   someid  someid  My wife

7   3   4   My Brother

8   6   5   My son.

我在寻找这样的输出

ID  MotherID    FatherID    Name        Level

1   someid  someid  grandfather 0


2   someid  someid  Father      1

3   someid  someid  Me      2


4   someid  someid  Brother     2

5   someid  someid  My Son      3

提前致谢。 克什米尔。

Answer 1:

试试这个：

;WITH FamilyCTE
AS
(
    SELECT 
      *, 
      CAST(NULL AS VARCHAR(50)) AS FatherName,  
      CAST(NULL AS VARCHAR(50)) AS MotherName, 0 AS Level
    FROM @FamilyTree
    WHERE FatherID IS NULL 
      AND MotherID IS NULL
    UNION ALL
    SELECT 
      f.ID, 
      f.Name AS ParentName, 
      f.MotherID, 
      f.FatherID, 
      c.Name AS FatherName, 
      c2.Name AS MotherName, 
      Level + 1
    FROM @FamilyTree AS F
    INNER JOIN FamilyCTE c ON F.FatherID = c.ID
    INNER JOIN @FamilyTree c2 ON f.MotherID = c2.ID
)

SELECT * FROM FamilyCTE

演示

如果想要得到像你在你的问题发布什么确切的输出只是忽略FatherName和MotherName并限制在选择SELECT * FROM FamilyCTE使用您要选择列，在锚查询起始外公WHERE ID = 1例如。 如下面的演示：

DEMO

Answer 2:

select t1.ID, t1.MotherID, 
t1.FatherID,
t1.name, 
t2.Name,
Level
from table1 t1
left outer join table2 t2 on t1.FatherID =t2.FatherID 

文章来源: family tree sql query