我正在尝试使用以下示例数据在SQL Server中进行递归CTE
Class Student
------ ------
English Sally <- Sally is what were searching for
English Peter <- Peter's on same Class as Sally
Swedish Peter <- Found because Peter's on this class
Dutch Peter <- Found because Peter's on this class
Finnish Harry <- Not found, no relation to class or student
Swedish Tim <- Found because Peter's on Swedish class
Spanish Lauren <- Not found, no relation to class or student
Spanish Colin <- Not found, no relation to class or student
所以我需要一个CTE,并将'Sally'作为参数传递给它,它将查找所有与Sally相关的不同班级,然后查找Sally所在的所有班级中的学生,然后查找与Sally所在班级中的学生相同的其他班级,依此类推,直到没有更多行被找到。但我无法想出如何编写连接语句,以下是我尝试过但失败惨败的方式:
WITH myCTE (Class, Student) AS
(
SELECT Class, Student FROM TABLE1 WHERE TABLE1.Student= 'Sally'
UNION ALL
SELECT t.Class, t.Student FROM TABLE1 t
JOIN myCTE t2 ON t2.Class = t.Class
)
SELECT * FROM myCTE