使用递归 CTE 解析组，而不是层次结构

Question

我正在尝试使用以下示例数据在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

Answer 1

第一个问题是你有无限递归：莎莉和彼得一起学习英语，谁和莎莉一起学习英语，谁和彼得一起学习英语......

解决该问题后，您将需要在递归 CTE 中增加一个查询。您目前正在加入Class以使其他学生进入同一班级，但您还需要加入Student才能为学生获得其他课程。

这样的事情应该有效：

WITH cteSource As
(
   SELECT
      Class,
      Student,
      -- Create a unique ID for each record:
      ROW_NUMBER() OVER (ORDER BY Student, Class) As ID
   FROM
      TABLE1
),
cteRecursive (Class, Student, IDPath) As
(
   SELECT
      Class,
      Student,
      -- Used to exclude records we've already visited:
      Convert(varchar(max), '/' + Convert(varchar(10), ID) + '/')
   FROM
      cteSource
   WHERE
      Student = 'Sally'
   UNION ALL
   -- Students in the same class:
   SELECT
      T.Class,
      T.Student,
      R.IDPath + Convert(varchar(10), T.ID) + '/'
   FROM
      cteSource As T
      INNER JOIN cteRecursive As R
      ON T.Class = R.Class
   WHERE
      CharIndex('/' + Convert(varchar(10), t.ID) + '/', R.IDPath) = 0
   UNION ALL
   -- Other classes for the students:
   SELECT
      T.Class,
      T.Student,
      R.IDPath + Convert(varchar(10), T.ID) + '/'
   FROM
      cteSource As T
      INNER JOIN cteRecursive As R
      ON T.Student = R.Student
   WHERE
      CharIndex('/' + Convert(varchar(10), t.ID) + '/', R.IDPath) = 0
)
SELECT
   Class,
   Student,
   IDPath
FROM
   cteRecursive
;

使用测试数据，你将获得以下结果：

English   Sally   /7/
English   Peter   /7/5/
Dutch     Peter   /7/5/4/
Swedish   Peter   /7/5/6/
Swedish   Tim     /7/5/6/8/
Dutch     Peter   /7/5/6/4/
Swedish   Peter   /7/5/4/6/
Swedish   Tim     /7/5/4/6/8/

如果您使用的是 SQL 2008 或更高版本，如果将IDPath设置为HierarchyID，您可能会获得更好的性能，但您需要使用实际数据进行测试。

编辑
您可能需要将最终选择更改为：

SELECT DISTINCT
   Class,
   Student
FROM
   cteRecursive

处理同一记录有多个路径的情况。例如，"荷兰语/彼得"、"瑞典语/彼得"和"瑞典语/蒂姆"都出现了两次。