Mysql WHERE IN 映射

Question

我有一个 Mysql 查询，请检查以下内容:

SELECT `tbl_classSubjects`.`classID` , `tbl_classSubjects`.`sectionID` ,
`tbl_classes`.`name` AS className, `tbl_sections`.`name` AS sectionName
FROM `tbl_classSubjects`
JOIN `tbl_classes` ON `tbl_classSubjects`.`classID` = `tbl_classes`.`classID`
JOIN `tbl_sections` ON `tbl_classSubjects`.`sectionID` = `tbl_sections`.`sectionID`
WHERE `tbl_classes`.`classID` IN ( 24, 24, 27, 28 )
AND tbl_sections.sectionID IN ( 19, 20, 19, 15 )
GROUP BY `tbl_classSubjects`.`classID` , `tbl_classSubjects`.`sectionID`
ORDER BY `className` , `sectionName`
LIMIT 0 , 30

产生的结果为:

classID sectionID className sectionName
27      19      6th         A

27      20      6th         B

27      15      6th         C

28      19      7th         A

28      20      7th         B

28      15      7th         C

24      19      LKG         A

24      20      LKG         B

这是错误的。我要映射

WHERE IN ( 24, 24, 27, 28 )
AND tbl_sections.sectionID IN ( 19, 20, 19, 15 )

as 24-19, 24-20, 27-19, 28-15 因此它产生的结果为

classID sectionID className sectionName
27      19      6th         A

28      15      7th         C

24      19      LKG         A

24      20      LKG         B

Answer 1

您要求的结果WHERE classID 是IN，或者是该组的成员 (24, 24, 27, 28 ) 并且 sectionID 是IN，或者是组 (19, 20, 19, 5) 的成员。

第一个表中的所有结果都符合这两个条件，这些条件完全不相互依赖，并且第一组中的 24 次和第二组中的 19 次重复是多余的。

根据您的描述，我假设您想要逻辑:

WHERE (classID = 24 AND sectionID = 19)
   OR (classID = 24 AND sectionID = 20)
   OR (classID = 27 AND sectionID = 19)
   OR (classID = 28 AND sectionID = 5)

为了进一步演示IN，可以这样写:

WHERE (classID = 24 AND sectionID IN (19,20))
   OR (classID = 27 AND sectionID = 19)
   OR (classID = 28 AND sectionID = 5)