如何在无递归SQL中查询Person10的二度邻居节点？

表结构与测试数据 #

CREATE TABLE friendships 
(
    id INT PRIMARY KEY,
    person VARCHAR(50),
    friend VARCHAR(50)
);

INSERT INTO friendships (id, person, friend) VALUES
(1, 'person3', 'person9'),
(3, 'person10', 'person4'),
(4, 'person2', 'person1'),
(5, 'person6', 'person7'),
(7, 'person4', 'person10'),
(8, 'person6', 'person7'),
(10, 'person10', 'person9'),
(11, 'person5', 'person10'),
(12, 'person3', 'person7'),
(13, 'person9', 'person5'),
(14, 'person9', 'person7'),
(15, 'person9', 'person5'),
(16, 'person3', 'person6'),
(17, 'person8', 'person9'),
(18, 'person10', 'person2'),
(19, 'person7', 'person5'),
(20, 'person10', 'person8');

需求 #

• 统计与person10为二度连接的节点总数（正确结果：8）
• 列出这些二度连接的具体节点（正确结果：person2、person1、person4、person8、person9、person5、person7、person3）

约束：使用不支持递归函数的旧版SQL，方案需可推广至n度邻居查询

错误尝试分析 #

之前的SQL仅考虑单向的f1.friend = f2.person关系，未处理友谊的双向性，同时错误排除了直接朋友（一度邻居），导致结果严重缺失。

错误查询语句及结果：

-- 统计数量的错误尝试
SELECT COUNT(DISTINCT f2.friend)
FROM friendships f1
JOIN friendships f2 ON f1.friend = f2.person
WHERE f1.person = 'person10' AND f2.friend != 'person10' AND f2.friend NOT IN (
    SELECT friend FROM friendships WHERE person = 'person10'
);
-- 结果：3

-- 查询具体节点的错误尝试
SELECT DISTINCT f2.friend
FROM friendships f1
JOIN friendships f2 ON f1.friend = f2.person
WHERE f1.person = 'person10' AND f2.friend != 'person10' AND f2.friend NOT IN (
    SELECT friend FROM friendships WHERE person = 'person10'
);
-- 结果：person5、person7、person1

正确SQL写法 #

1. 查询二度连接的具体节点 #

SELECT DISTINCT 
    CASE 
        WHEN f.person = fd.first_degree THEN f.friend 
        ELSE f.person 
    END AS second_degree_neighbor
FROM (
    -- 先获取person10的所有直接朋友（一度邻居）
    SELECT DISTINCT 
        CASE 
            WHEN person = 'person10' THEN friend 
            ELSE person 
        END AS first_degree
    FROM friendships
    WHERE person = 'person10' OR friend = 'person10'
) fd
-- 关联一度邻居的所有双向关联关系
JOIN friendships f 
    ON f.person = fd.first_degree OR f.friend = fd.first_degree
-- 排除目标节点自身
WHERE second_degree_neighbor != 'person10'
ORDER BY second_degree_neighbor;

执行结果：

second_degree_neighbor
-----------------------
person1
person2
person3
person4
person5
person7
person8
person9

2. 统计二度连接的节点数量 #

SELECT COUNT(DISTINCT 
    CASE 
        WHEN f.person = fd.first_degree THEN f.friend 
        ELSE f.person 
    END
) AS second_degree_count
FROM (
    SELECT DISTINCT 
        CASE 
            WHEN person = 'person10' THEN friend 
            ELSE person 
        END AS first_degree
    FROM friendships
    WHERE person = 'person10' OR friend = 'person10'
) fd
JOIN friendships f 
    ON f.person = fd.first_degree OR f.friend = fd.first_degree
WHERE CASE 
        WHEN f.person = fd.first_degree THEN f.friend 
        ELSE f.person 
    END != 'person10';

执行结果：8

推广至n度邻居的R实现思路 #

由于旧版SQL不支持递归，可通过R循环调用SQL逐步扩展邻居范围：

1. 初始化：获取目标节点的一度邻居，存入数据框
2. 循环n-1次：每次基于当前邻居集合，查询它们的所有关联节点，去重后排除已发现的节点和目标节点，更新邻居集合
3. 循环结束后，汇总所有n度内的邻居

示例R代码框架：

library(DBI)
# 假设已建立数据库连接conn
target <- "person10"
n_degree <- 2

# 初始化一度邻居
current_neighbors <- dbGetQuery(conn, sprintf("
    SELECT DISTINCT 
        CASE WHEN person = '%s' THEN friend ELSE person END AS neighbor
    FROM friendships
    WHERE person = '%s' OR friend = '%s'
", target, target, target))$neighbor

all_neighbors <- current_neighbors

if(n_degree > 1) {
    for(i in 2:n_degree) {
        # 构造当前邻居的IN条件
        neighbor_list <- paste0("'", paste(current_neighbors, collapse = "','"), "'")
        # 查询当前邻居的关联节点
        new_neighbors <- dbGetQuery(conn, sprintf("
            SELECT DISTINCT 
                CASE WHEN person IN (%s) THEN friend ELSE person END AS neighbor
            FROM friendships
            WHERE person IN (%s) OR friend IN (%s)
        ", neighbor_list, neighbor_list, neighbor_list))$neighbor
        # 去重：排除目标节点和已发现的邻居
        new_neighbors <- setdiff(new_neighbors, c(target, all_neighbors))
        # 更新集合
        all_neighbors <- c(all_neighbors, new_neighbors)
        current_neighbors <- new_neighbors
        # 无新节点则提前终止
        if(length(new_neighbors) == 0) break
    }
}

# 输出结果
cat("n度邻居数量：", length(all_neighbors), "\n")
cat("n度邻居列表：", paste(all_neighbors, collapse = ", "), "\n")

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