mysql SQL：使用JOINS、ANY或IN从源节点获取一级、二级和三级节点

tf7tbtn2 于 2023-01-20 发布在 Mysql

关注(0)|答案(2)|浏览(127)

我在MySQL数据库中有名为course、student和students_in的表。
表格如下所示：

- 课程**

course_id       name
    3             Physics
   12             English
   19             Basket Weaving
    4             Computer Science
  212             Discrete Math
  102             Biology
   20             Chemistry
   50             Robotics
    7             Data Engineering

- 学生**

id    name
 2    Sally
 1    Bob
17    Robert
 9    Pierre
12    Sydney
41    James
22    William
 5    Mary
 3    Robert
92    Doris
 6    Harry

- 学生_在**

course_id   student_id      grade
    3              2              B
  212              2              A
    3             12              A
   19             12              C
    3             41              A
    4             41              B
  212             41              F
   19             41              A
   12             41              B
    3             17              C
    4              1              A
  102              1              D
  102             22              A
   20             22              A
   20              5              B
   50              3              A
   12             92              B
   12             17              C
    7              6              A

这里有一个小提琴：http://sqlfiddle.com/#!9/d3f991/1
我的目标是让以下学生获得id和name：
1.与Sally一起参加过课程（即"一级"关系），或
1.与Sally一起上过课的人一起上过课（即"二级"关系），或
1.与曾与Sally一起上过课的人一起上过课（即"第三关系"关系）
基本上，我们要找的是与莎莉的一级、二级和三级关系。
下面是对这在逻辑上的描述：

由于Sally选修了课程ID 3和212，所需的结果将如下所示（不是上面的彩色表格，我提供该表格是为了说明所涉及的逻辑）：

student_id       student_name
    12           Sydney             <-- took course ID 3 with Sally
    41           James              <-- took course ID 3 and 212 with Sally
    17           Robert             <-- took course ID 3 with Sally
     1           Bob                <-- took course ID 4 with James
    92           Doris              <-- took course ID 12 with James and Robert
   102           William            <-- took course ID 102 with Bob

- 除了使用recursive CTE之外，是否可以使用更简化的方法获得所需的输出，例如以下一种或多种方法？**

JOINS和subqueries
1.使用ANY或IN运算符
谢谢大家!

mysql

来源：https://stackoverflow.com/questions/75175567/sql-using-joins-any-or-in-to-get-first-second-and-third-degree-nodes-from-a

2条答案

按热度按时间

tv6aics11#

您可以合并单独的度数并对生成的名称进行分组/排序：

select `name` from (
    (
    select student.`name`
    from student sally
    join students_in sally_course
    on sally.id = sally_course.student_id
    join students_in si1
    on sally_course.course_id = si1.course_id
    join student
    on si1.student_id = student.id
    where student.`name` <> 'sally'
    )
    union
    (
    select student.`name`
    from student sally
    join students_in sally_course
    on sally.id = sally_course.student_id
    join students_in si1
    on sally_course.course_id = si1.course_id
    join students_in si2
    on si1.course_id = si2.course_id
    join student
    on si2.student_id = student.id
    where student.`name` <> 'sally'
    )
    union
    (
    select student.`name`
    from student sally
    join students_in sally_course
    on sally.id = sally_course.student_id
    join students_in si1
    on sally_course.course_id = si1.course_id
    join students_in si2
    on si1.course_id = si2.course_id
    join students_in si3
    on si2.course_id = si3.course_id
    join student
    on si3.student_id = student.id
    where student.`name` <> 'sally'
    )) t
    group by `name`
    order by `name`

赞(0）回复(0）举报 2023-01-20

8aqjt8rx2#

请让我知道它是否适合您：http://sqlfiddle.com/#!9/d3f991/14

赞(0）回复(0）举报 2023-01-20