使用递归CTE解析组而不是层次结构 [英] Using recursive CTE to resolve a group, not hierarchy

问题描述

我正在尝试使用以下示例数据在SQL Server中进行递归CTE

 班级学生
 ------ ------ 
的英语Sally<-Sally正在寻找
的英语Peter<-彼得与Sally处于同一个阶级
瑞典语Peter< -因彼得在该课程上
荷兰人彼得而找到--因彼得在该课程上
芬兰哈利而找到--未找到，与班级或学生没有关系
瑞典语蒂姆<-发现因为彼得在瑞典语课堂上
西班牙文Lauren<-未找到，与班级或学生没有关系
西班牙文科林<-未找到，与班级或学生没有关系
  

所以我需要一个CTE，将'Sally'作为参数赋予它，它将找出与Sally相关的所有不同类，然后与Sally所在班级相关的学生，然后与Sally在同一班级中的所有学生相关的课程，依此类推

但是我只是无法弄清楚如何编写联接，这是我尝试过的尝试，但失败了：

 使用myCTE（班级，学生）作为
（
 SELECT班级，学生在TABLE1中，TABLE1.Student ='Sally'
 UNION ALL 
从表1中选择t.Class，t.student t 
在t2.Class = t.Class 
上加入myCTE t2）
 SELECT * FROM myCTE 
  

解决方案

第一个问题是您拥有无限递归：Sally需要英语与彼得（英语）和萨莉（英语）一起学习，与彼得（萨利）和英语（英语）一起学习。 CTE。您目前正在加入班来获得同一班的其他学生，但是您还需要加入学生为学生准备其他课程。

类似的方法应该起作用：

 与cteSource作为
（
 SELECT 
 Class，
 Student，
-为每条记录创建唯一的ID：
 ROW_NUMBER（ ）OVER（按学生，班级顺序）作为ID 
从
表1 
），
 cteRecursive（班级，学生，IDPath）作为
（
 SELECT 
类，
学生，
-用于排除我们已经访问过的记录：
 Convert（varchar（max），'/'+ Convert（varchar（10）， ID）+'/'）
来自
 cteSource 
 WHERE 
 Student ='Sally'
 
 UNION ALL 
 
- -同一班级的学生：
 SELECT 
 T.Class，
 T.Student，
 R.IDPath + Convert（varchar（10），T.ID）+'/ '
来自
 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 
 
-学生的其他班级：
选择
 T.Class，
 T.Student，
 R.IDPath + Convert（varchar（10），T.ID）+'/'
 FROM 
 cteSource as T 
内部联接cteRecursive As R 
 T.Student = R.Student 
 WHERE 
 CharIndex（'/'+ Convert（varchar（10），t.ID）+'/'，R .IDPath）= 0 
）
选择
班级，
学生，
 IDPath 
 FROM 
 cteRecursive 
; 
  

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

 英语Sally / 7 / 
英语Peter / 7/5 / 
荷兰语Peter / 7/5/4 / 
瑞典语彼得/ 7/5/6 / 
瑞典蒂姆/ 7/5/6/8 / 
荷兰彼得/ 7/5/6/4 / 
瑞典彼得/ 7/5/4 / 6 / 
瑞典语Tim / 7/5/4/6/8 / 
  

如果您使用的是SQL 2008或更高版本，如果将 IDPath 设置为 HierarchyID ，可能会获得更好的性能，但是

编辑

您可能需要将最终选择内容更改为：

 选择区域
班级，
学生
从
 cte递归
  

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

I'm trying to do a recursive CTE in SQL Server with the following example data

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

So I need a CTE, to which I give 'Sally' as parameter, and it will find out all different classes related to Sally, then all Students related to Classes that Sally is in, then all classes related to students in the same classes as Sally, and so on until no more rows found.

But I just cannot figure out how to write the joins, this is what I tried but failed miserably:

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

解决方案

The first problem is that you've got infinite recursion: Sally takes English with Peter, who takes English with Sally, who takes English with Peter...

Once you've sorted that out, you'll need an additional query in your recursive CTE. You're currently joining on Class to get the other students in the same class, but you also need to join on Student to get the other classes for the students.

Something like this should work:

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
;

With your test data, you'll get the following results:

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/

If you're using SQL 2008 or higher, you might get better performance if you make the IDPath a HierarchyID, but you'd need to test with your real data.

EDIT
You might need to change the final select to:

SELECT DISTINCT
   Class,
   Student
FROM
   cteRecursive

to deal with cases where there are multiple paths to the same record. For example, "Dutch/Peter", "Swedish/Peter" and "Swedish/Tim" all appear twice.

这篇关于使用递归CTE解析组而不是层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！