mysql - 如何对多个表中的一条记录进行 GROUP

Question

我有两个表类(class)和科目。这是一对多的比例，因为每节课都可以分为 1 个或多个科目，从而产生类似于“名称”科目的内容:数学、科学、社会研究

这将返回 3 条记录:
姓名、数学
名称，科学
姓名、社会研究

这会导致我循环和组合并弄乱我的搜索计数
我怎样才能获得一张记录同时包含所有三张记录？那么我的搜索计数是准确的，我不需要额外的 PHP 代码来检查名称是否相同并收集额外的主题？

我尝试过基本的

SELECT * FROM lesson INNER JOIN subjects ON subject.id = lesson.subject

但这会产生 3 个条目

编辑
我的查询比我引导的更复杂。我有一个中间表跟踪上面的两个表及其关系
此查询用于搜索。这是我所拥有的。

SELECT name, subject        
FROM lesson As l
INNER JOIN lesson_sub As ls
  ON ls.lesson_id = l.id
INNER JOIN subjects As s
  ON s.id = ls.subject_id
WHERE CONCAT(l.name, s.subject) LIKE '%KEYWORD%' AND s.id = SUBJECT_ID

Answer 1

您可以将 GROUP_CONCAT() 函数与 JOIN 查询结合使用:

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

GROUP_CONCAT(expr)

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values. The full syntax is as follows:

GROUP_CONCAT([DISTINCT] expr [,expr ...] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name ...]] [SEPARATOR str_val])

mysql> SELECT student_name, -> GROUP_CONCAT(test_score) -> FROM student -> GROUP BY student_name;

Or:

mysql> SELECT student_name, -> GROUP_CONCAT(DISTINCT test_score -> ORDER BY test_score DESC SEPARATOR ' ') -> FROM student -> GROUP BY student_name;

所以:

SELECT lesson.name, GROUP_CONCAT(subjects.name SEPARATOR ', ')
    FROM lesson JOIN subjects ON (subject.id = lesson.subject)
    GROUP BY lesson.name;

测试

CREATE TABLE lesson ( name varchar (20), subject integer );
CREATE TABLE subjects ( id integer, name varchar(20) );

INSERT INTO subjects VALUES ( 1, 'Math' ), ( 2, 'Physics' ), ( 3, 'Chemistry' );

INSERT INTO lesson VALUES ( 'Lesson A', 1 );
INSERT INTO lesson VALUES ( 'Lesson A', 2 );
INSERT INTO lesson VALUES ( 'Lesson A', 3 );
INSERT INTO lesson VALUES ( 'Lesson B', 2 );
INSERT INTO lesson VALUES ( 'Lesson B', 3 );
INSERT INTO lesson VALUES ( 'Lesson C', 1 );

SELECT lesson.name, GROUP_CONCAT(subjects.name SEPARATOR ', ')
    FROM lesson JOIN subjects ON (subjects.id = lesson.subject)
    GROUP BY lesson.name;

+----------+--------------------------------------------+
| name     | GROUP_CONCAT(subjects.name SEPARATOR ', ') |
+----------+--------------------------------------------+
| Lesson A | Math, Chemistry, Physics                   |
| Lesson B | Chemistry, Physics                         |
| Lesson C | Math                                       |
+----------+--------------------------------------------+
3 rows in set (0.00 sec)

更复杂的测试(带有中间表)

CREATE TABLE lesson ( id integer, name varchar (20) );
CREATE TABLE subjects ( id integer, name varchar(20) );
CREATE TABLE lesson_sub ( lesson_id integer, subject_id integer );

INSERT INTO subjects VALUES ( 1, 'Math' ), ( 2, 'Physics' ), ( 3, 'Chemistry' );
INSERT INTO lesson VALUES ( 1, 'Lesson A' ), ( 2, 'Lesson B' ), ( 3, 'Lesson C' );

INSERT INTO lesson_sub VALUES (1,1), (1,2),(1,3),(2,2),(2,3),(3,1);

SELECT lesson.name, GROUP_CONCAT(subjects.name SEPARATOR ', ') AS subjects
    FROM lesson_sub JOIN lesson ON ( lesson.id = lesson_sub.lesson_id )
                    JOIN subjects ON (subjects.id = lesson_sub.subject_id)
    WHERE CONCAT(lesson.name, subjects.name) LIKE '%Chem%'
    GROUP BY lesson.name;

SELECT name, subjects FROM (
    SELECT lesson.name, GROUP_CONCAT(subjects.name SEPARATOR ', ') AS subjects
    FROM lesson_sub JOIN lesson ON ( lesson.id = lesson_sub.lesson_id )
                    JOIN subjects ON (subjects.id = lesson_sub.subject_id)
    GROUP BY lesson.name ) AS lesson_clear
    WHERE CONCAT(name, subjects) LIKE '%Chem%';

+----------+--------------------------------------------+
| name     | GROUP_CONCAT(subjects.name SEPARATOR ', ') |
+----------+--------------------------------------------+
| Lesson A | Chemistry                                  |
| Lesson B | Chemistry                                  |
+----------+--------------------------------------------+
2 rows in set (0.00 sec)

+----------+--------------------------+
| name     | subjects                 |
+----------+--------------------------+
| Lesson A | Physics, Math, Chemistry |
| Lesson B | Physics, Chemistry       |
+----------+--------------------------+
2 rows in set (0.00 sec)