基于R语言sqldf构建家族树嵌套父子关系的技术问询

我来帮你实现这个需求，结合你提到的递归CTE和sqldf的用法，咱们一步步来：

首先先把你的家族数据整理成R的数据框：

library(sqldf)

# 构建你的家族数据
family_data <- data.frame(
  person = c("Guillou Arthur", "Cleach Marc", "Guillou Eric", "Guillou Jacques",
             "Cleach Franck", "Cleach Leo", "Cleach Herbet", "Cleach Adele",
             "Guillou Jean", "Guillou Alan"),
  father = c(NA, NA, "Guillou Arthur", "Guillou Arthur",
             "Cleach Marc", "Cleach Marc", "Cleach Leo", "Cleach Herbet",
             "Guillou Eric", "Guillou Eric"),
  stringsAsFactors = FALSE
)

接下来用递归CTE完成查询：锚点部分定位顶层人物Guillou Arthur，递归部分逐层关联子节点并记录层级，最后按层级排序输出：

# 递归CTE查询逻辑
result <- sqldf("
  WITH RECURSIVE family_tree AS (
    -- 锚点：选中顶层人物，层级设为0
    SELECT person, father, 0 AS level
    FROM family_data
    WHERE person = 'Guillou Arthur'
    
    UNION ALL
    
    -- 递归：关联父节点，层级递加1
    SELECT fd.person, fd.father, ft.level + 1 AS level
    FROM family_data fd
    JOIN family_tree ft ON fd.father = ft.person
  )
  -- 按层级、姓名排序输出
  SELECT person, father, level
  FROM family_tree
  ORDER BY level, person
")

# 查看最终结果
print(result)

结果说明 #

执行后会得到如下结构化输出：

person          father level
1 Guillou Arthur           <NA>     0
2  Guillou Eric Guillou Arthur     1
3 Guillou Jacques Guillou Arthur     1
4   Guillou Alan  Guillou Eric     2
5   Guillou Jean  Guillou Eric     2

逻辑拆解：

• 锚点成员先锁定顶层的Guillou Arthur，标记其层级为0；
• 递归成员通过父节点关联，把当前节点的所有子节点找出来，层级在父节点基础上加1；
• 最后按层级升序、姓名排序，完美呈现家族树的层级结构。

内容的提问来源于stack exchange，提问作者Wilcar