mysql - WHERE 子句中的操作 AND OR

Question

我在获取数据时遇到了问题。我想找到所有包含指定标志的类(class)。如何编写 SQL 代码来查找标记相同类型的类(class)是 OR 条件，而其他类型是 between AND 条件。

类(class)表的记录:

+----+----------+
| id |   name   |
+----+----------+
| 1  |  Spring  |
| 2  | Hibernate|
| 3  |  LATEX   |
+----+----------+

course_flags 表的记录:

+----+----------+----------+
| id |course_id |  flag_id |
+----+----------+----------+
| 1  |     1    |     1    |
| 2  |     1    |     2    |
| 3  |     1    |     3    |
| 4  |     2    |     2    |
| 5  |     2    |     3    |
+----+----------+----------+

flag 表的记录:

+----+-------------+-------------+
| id |     name    |     type    |
+----+-------------+-------------+
| 1  |  promotion  |   NORMAL    |
| 2  | last minute |   NORMAL    |
| 3  |   Samsung   |ACCREDITATION|
| 4  |    Nokia    |ACCREDITATION|
+----+-------------+-------------+

我的查询

 SELECT course.id,
      course.course_name,
      GROUP_CONCAT(Flags.id SEPARATOR ',') as flags
 FROM course
 INNER JOIN course_flags ON 
    course.id = course_flags.course_id
 INNER JOIN flags ON 
    flags.id = course_flags.flags_id
 AND ((flags.id =1 OR flags.id =2) AND flags.id = 3)
 GROUP BY course.id, course.course_name

不幸的是，我的数据库引擎返回了零条记录。

我本来希望得到这样的结果:

+----+----------+----------+
| id |   name   |   flags  |
+----+----------+----------+
| 1  | Spring   |  1,2,3   |
| 2  | Hibernate|   2,3    |
+----+----------+----------+

请问如何获取？

提前致谢!

Answer 1

已回答your previous question , JOIN 谓词(与 WHERE 子句一样)限制记录 prior 进行分组；而 HAVING 子句限制分组后的结果:

SELECT   course.id, course.name, GROUP_CONCAT(flags.id) flags
FROM     course
    JOIN course_flags ON course_flags.course_id = course.id
    JOIN flags ON flags.id = course_flags.flag_id
GROUP BY course.id
HAVING   SUM(flags.id IN (1,2)) AND SUM(flags.id = 3)

查看sqlfiddle .