递归SQL查询问题：如何将指定学生信息固定输出至室友结果

问题分析 #

你当前的递归查询没有保留目标学生（Smith）的固定信息，而是将Smith和室友的信息混在同一字段里，导致无法输出固定的Smith信息。实际上这个场景不需要递归——找同宿舍室友是简单的自关联需求，递归更适合处理层级关系（如上下级、家谱）。

非递归解法（推荐） #

直接通过自关联获取目标学生的室友，同时保留目标学生的固定信息：

SELECT 
    s.lastName AS target_lastName,
    s.homeAdd AS target_homeAdd,
    CONCAT(r.firstName, ' ', r.lastName) AS roommate_name,
    r.homeAdd AS roommate_homeAdd
FROM college s
JOIN college r 
    ON s.dormRoom = r.dormRoom  -- 关联同宿舍的学生
    AND s.StuID != r.StuID      -- 排除目标学生自己
WHERE s.lastName = 'Smith';     -- 指定筛选的目标姓氏

逻辑说明：

• s 表代表目标学生（Smith），获取其姓氏、住址和宿舍号
• r 表代表室友，通过宿舍号关联，同时用StuID排除目标学生自身
• 输出时直接拼接室友的姓名，同时保留目标学生的固定信息

递归解法（若需保留递归写法） #

如果坚持用递归，需要在递归CTE中始终传递目标学生的固定信息，避免丢失：

WITH RECURSIVE roommates AS (
    -- 初始查询：获取目标学生信息，并标记为固定字段
    SELECT 
        lastName AS target_lastName,
        homeAdd AS target_homeAdd,
        firstName,
        lastName,
        homeAdd,
        dormRoom,
        StuID
    FROM college WHERE lastName = 'Smith'
    UNION
    -- 递归关联：传递目标学生的固定信息，同时获取室友数据
    SELECT 
        r.target_lastName,
        r.target_homeAdd,
        c.firstName,
        c.lastName,
        c.homeAdd,
        c.dormRoom,
        c.StuID
    FROM roommates r
    JOIN college c 
        ON c.dormRoom = r.dormRoom 
        AND c.StuID != r.StuID  -- 避免重复关联自身
)
-- 最终查询：排除目标学生自己，输出所需字段
SELECT 
    target_lastName,
    target_homeAdd,
    CONCAT(firstName, ' ', lastName) AS roommate_name,
    homeAdd AS roommate_homeAdd
FROM roommates
WHERE StuID != (SELECT StuID FROM college WHERE lastName = 'Smith');

输出结果 #

两种方法都会得到符合你需求的输出：

Smith     345 Juniper St     John Johnson 237 Maple St
Smith     345 Juniper St     Susi Sousa   1024 NE 76th Pl

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