在 MYSQL 中查找记录 And:
想找到 的学生 ID,必须具有 (professorId = 3 And courseId = 4) And (professorId = 3 And courseId = 2) And (professorId = 5 And courseId = 8)。 有时这些括号是 1 个或多个 20 个
有两个 table 类和 studentsClasses 是这样的:
studentsClasses 表:
id | studentId | classId
1 1 2
2 1 3
3 1 4
4 2 3
5 2 5
6 3 2
7 4 3
8 4 2
9 1 8
班级表:
id | professorId | CourseId
1 1 1
2 3 4
3 2 5
4 3 2
5 4 2
6 2 3
7 5 6
8 5 8
9 5 9
如果每个括号都用作 or,那就非常简单了。但我想找到一个以 and 的形式拥有所有这些东西的学生。
此查询是 “OR” 方案:
select `studentsClasses`.`studentId` from `studentsClasses`
inner join `class` on (`studentsClasses`.`classId`=`class`.`id`
AND ((professorId = 3 And courseId = 4) OR (professorId = 3 And courseId = 2)
OR (professorId = 5 And courseId = 8)))
但想要这样的 AND 场景:
select `studentsClasses`.`studentId` from `studentsClasses`
inner join `class` on (`studentsClasses`.`classId`=`class`.`id`
AND ((professorId = 3 And courseId = 4) AND (professorId = 3 And courseId = 2)
AND (professorId = 5 And courseId = 8)))
但上面的问题是错误的。
例如,具有这些值的结果查询应为:
studentId
---------
1