使用递归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

- Jaska

---

请添加预期输出。 - TechDo

在这种情况下，预期结果是示例数据中的所有字段，因为值1有'A'和'B'，而值'B'的Field1中有值1、2和3。我的示例代码问题在于它会无限循环，并且在没有找到新出现时不会停止。 - Jaska

我认为你的数据集太小了，无法清楚地说明你想要做什么。请添加一些行，例如(4,C) (3,D) (10,A)，并告诉我们查询应该从该输入输出什么（以及为什么）。 - sebt

好的，已经编辑并写出了应该找到哪些行。我可以使用while循环或游标来完成，但我认为使用CTE会更专业！ - Jaska

Answer 1

第一个问题是你遇到了无限递归：Sally 和 Peter 同时上英语课，而 Sally 又和 Peter 一起上英语课，这样就形成了无限递归。

一旦你解决了这个问题，你需要在递归 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

处理存在多条路径指向同一记录的情况。例如，“荷兰语/Peter”，“瑞典语/Peter”和“瑞典语/Tim”都出现了两次。

- Richard Deeming

---

是的，我尝试了一下，似乎可以工作！明天我会进行更多测试，如果这是正确的方法，我会接受你的答案。谢谢！ - Jaska