时间戳

首页 > 解决方案 > 获取精确匹配结果的 SQL 查询

mysql - 获取精确匹配结果的 SQL 查询

问题描述

在我的 MySQL 数据库中，我有三个表students, classes, courses.

一class有多students。

一class有多courses。

该courses表有一个布尔字段active和一个字符串字段name。

所以整体关系是（对不起，如果不清楚，我不确定如何更好地说明这种关系）：

students (many_to_one) classes (one_to_many) courses

我在 Ruby 中有一个函数，它接受 coursename的字符串参数数组：

def get_student_names_whose_courses_are(active_course_names)
   # Run a raw SQL query for the result
end

我想编写一个原始 SQL 查询来获取students其courses（通过class）的名称与传入的参数课程名称完全匹配并且处于活动状态。

例如，如果active_course_names持有值['foo','bar']。学生 A 有活动课程 'foo'、'bar'、'etc'，学生 B 有活动课程 'foo' 和 'bar'。原始查询应该只返回学生 B，即使学生 A 也有两门课程处于活动状态，重点是完全匹配。

我尝试的是这样的：

select stu.name
from students stu
inner join classes clz ON clz.id = stu.class_id
inner join courses cour ON cour.class_id = clz.id AND cour.name in (#{active_course_names,join("','")})
where cour.active = true;

但是这样一来，对于上面的示例，它会同时返回学生 A 和学生 B。如何进行查询以使其返回具有完全活动课程的学生？

我的演示示例在这里

标签： mysqlsqlrelational-division

解决方案

希望下面的回答对您的问题有所帮助。

如果您在相同的课程中指定排名（例如“FOO”），那么您可以保留课程中重复的课程（排名>1）并应用您的课程列表。创建之后，您可以执行加入以提供您需要的学生信息。

SELECT Temp.name,stud.id,stud.name,stud.class_id
FROM (
  /*Create a view with the courses which are present with another classroom*/
  SELECT T.id, T.name, T.active,T.class_id
  FROM (
    /*Create a view with only the active courses and their rank*/
    SELECT id,name,active,class_id,
      @course_rank := IF(@current_course = name,
      @course_rank + 1, 1) AS course_rank,
      @current_course := name
    FROM courses,(select @current_course :=0,@course_rank :=0)  r
    WHERE active = 1
    ORDER BY name, class_id ASC)T
  /*Create a filter to bring only the classrooms with the same courses and the selected courses*/
  WHERE T.course_rank>1 AND T.name IN ('foo','bar'))Temp
JOIN students stud
ON Temp.class_id=stud.class_id

推荐阅读