无法理解GROUP_CONCAT

I cannot seem to get an GROUP_CONCAT query to work.

我似乎无法获得GROUP_CONCAT查询。

I have this tables, which I joined:

我有这个表,我加入了:

  stud
id | stud_name  |         
-----------------
1  | Class1     | 
2  | Class2     | 

  note
id | stud_id    |   mat_id       |   Note      | 
------------------------------------------------
1  | 1          |      1         |  10         | 
2  | 1          |      2         |  9          | 
3  | 1          |      1         |  10         |

mat
id | mat_name | 
----------------
1  | Porc     | 
2  | Vaca     |

Here is what I did to join them.

这是我加入他们所做的。

 SELECT
             `stud`.`id`
            , `mat`.`mat_name`
            , `note`.`note`

    FROM
            `stud`
   LEFT JOIN 
            `note` 
    ON
             (`stud`.`id` = `note`.`id_stud`)
   LEFT JOIN 
            `mat` 
        ON 
             (`note`.`id_mate` = `mat`.`id`) 

Here is what I want them to look.

这是我希望他们看的东西。

mat.id  | mat_name | Note      | 
-----------------------------
1       | Porc     | 10,10     | 
2       | Vaca     | 9         |

Here is how they look.

这是他们的样子。

mat.id  | mat_name | Note      | 
-----------------------------
1       | Porc     | 10        | 
2       | Vaca     | 9         | 
1       | Port     | 10        |

I tried doing this.

我试过这样做。

 SELECT 
 `mat`.`mat_name`,
 GROUP_CONCAT(`note`.`note`) AS `note`.`note`,
FROM ( "Here is what I did to join them." )attr_groups
GROUP BY `mat`.`mat_name`;
ORDER BY `mat`.`mat_name`;

Any ideas?

EDIT: If I add group by or ORDER by, no results are found. If I don't add them, the query works but its not concatenating them.

编辑:如果我添加group by或ORDER by,则找不到任何结果。如果我不添加它们,查询可以工作,但它不会连接它们。

3 个解决方案

#1

SELECT mat.id,
       mat.mat_name,
       GROUP_CONCAT(note.note) as note
FROM mat
LEFT JOIN note ON mat.id = note.mat_id
LEFT JOIN stud ON note.stud_id = stud.id     
GROUP BY mat.id, mat.mat_name
ORDER BY mat.mat_name

SQLFiddle demo

#2

You have error in group by.

你在组中有错误。

GROUP BY `mat`.`mat_name`;
------------------------^^^---

remove ; from query

去掉 ;来自查询

SELECT
     `stud`.`id`
    , `mat`.`mat_name`
    , GROUP_CONCAT(`note`.`note`) AS Note
FROM
    `stud`
LEFT JOIN 
    `note` 
ON
     (`stud`.`id` = `note`.`id_stud`)
LEFT JOIN 
    `mat` 
ON 
     (`note`.`id_mate` = `mat`.`id`) 
GROUP BY
    `stud`.mat_id
ORDER BY
    `mat`.`mat_name`

#3

Try this:

SELECT
    m.id,
    m.mat_name,
    GROUP_CONCAT(n.Note) as grades
FROM
    mat m
LEFT JOIN note n
    ON n.mat_id = m.id
GROUP BY
    m.id