GROUP_CONCAT 和 INNER JOIN 与 WHERE 子句 [英] GROUP_CONCAT and INNER JOIN with WHERE clause

问题描述

我在获取数据时遇到了问题.我想找到在 where 子句中包含标志的所有课程，并获取它包含的所有标志课程.

I've got a problem with getting data. I would like to find all courses, which contain the flag in where clause and get all flags course it contains.

课程表的记录:

+----+----------+
| id |   name   |
+----+----------+
| 1  | Spring   |
| 2  | Hibernate|
| 3  | C#       |
+----+----------+

course_flags 表的记录:

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

flag 表的记录:

+----+------------+
| id |    name    |
+----+------------+
| 1  | promocja   |
| 2  |last minute |
+----+------------+

我的查询

SELECT course.id, course.name,
       GROUP_CONCAT(Flag.id SEPARATOR ',') as flags
FROM course
INNER JOIN course_flags ON 
    course.id = course_flags.course_id
INNER JOIN flag ON 
    flag.id = course_flags.flag_id
WHERE flag.name LIKE 'promocja'
GROUP BY Course.id

不幸的是，我的数据库引擎返回的记录在 flags 列中有一个标记:

Unfortunately my database engine returned records with one flag in flags column:

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

我曾希望得到这样的结果:

I had hoped to get this result:

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

问题我如何获得它?

提前致谢！

推荐答案

WHERE 子句限制记录先于分组；而 HAVING 子句限制 after 分组后的结果:

The WHERE clause restricts records prior to grouping; whereas the HAVING clause restricts results after grouping:

SELECT   course.id, course.name, GROUP_CONCAT(Flag.id) flags
FROM     course
    JOIN course_flags ON course_flags.course_id = course.id
    JOIN flag ON flag.id = course_flags.flag_id
GROUP BY Course.id
HAVING   SUM(flag.name = 'promocja')

在 sqlfiddle 上查看.

这篇关于GROUP_CONCAT 和 INNER JOIN 与 WHERE 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！