mysql group_concat 对多个字段进行分组

我有一个表成员member_id, member_name, club_name, region, zone, email作为字段。

我正在使用MySQLgroup_concat功能类似于

SELECT group_concat(distinct m.email
SEPARATOR ', ' ) from member m group by m.club_name

这工作正常。但我希望能够group_concat在其他字段上，而不创建额外的查询。

是否可以提供其他字段作为参数？

member_id   member_name club_name   region  zone    email
1           member1           A        1    1   [email protected]
2           member2           A        1    1   [email protected]
3           member3           B        1    1   [email protected]
4           member4           C        1    2   [email protected]
5           member5           D        2    1   [email protected]

**group by club**
[email protected],[email protected]
[email protected]
[email protected]
[email protected]

**group by region**
[email protected], [email protected], [email protected], [email protected]
[email protected]

**group by zone**
[email protected], [email protected], [email protected]
[email protected]

假设每个大区有 3 个区域，每个区域有多个俱乐部。现在我怎样才能获得可以分组或与地区、区域或俱乐部相关的电子邮件？

从你的问题中很难理解你到底想要什么，但你可以尝试

SELECT club_name,
       GROUP_CONCAT(DISTINCT email SEPARATOR ', ' ) emails,
       GROUP_CONCAT(DISTINCT member_name SEPARATOR ', ' ) members
       ... 
 FROM member
GROUP BY club_name

示例输出：

| CLUB_NAME |                                EMAILS |          MEMBERS |
------------------------------------------------------------------------
|     Club1 | [email protected], [email protected], [email protected] | Jhon, Mark, Beth |
|     Club2 |              [email protected], [email protected] |    Helen, Thomas |
  

Here is SQLFiddle demo

附注： 在这样的问题中提供示例数据和所需的输出通常会改进您的更改，从而更快地获得答案并且最适合您的需求。

UPDATE:您可以使用深度打包信息GROUP_CONCAT()如果您想要的话，可以使用不同的分隔符

SELECT 'club' group_type, GROUP_CONCAT(details SEPARATOR '|') details
  FROM
(
  SELECT CONCAT(club_name, ';', GROUP_CONCAT(DISTINCT email)) details
    FROM member
   GROUP BY club_name
) a
UNION ALL
SELECT 'region' group_type, GROUP_CONCAT(details SEPARATOR '|') details
  FROM
(
  SELECT CONCAT(region, ';', GROUP_CONCAT(DISTINCT email)) details
    FROM member
   GROUP BY region
) a
UNION ALL
SELECT 'zone' group_type, GROUP_CONCAT(details SEPARATOR '|') details
  FROM
(
  SELECT CONCAT(zone, ';', GROUP_CONCAT(DISTINCT email)) details
    FROM member
   GROUP BY zone
) a

示例输出：

| GROUP_TYPE |                                                                                                DETAILS |
-----------------------------------------------------------------------------------------------------------------------
|       club | A;[email protected],[email protected]|B;[email protected]|C;[email protected]|D;[email protected] |
|     region |     1;[email protected],[email protected],[email protected],[email protected]|2;[email protected] |
|       zone |     1;[email protected],[email protected],[email protected],[email protected]|2;[email protected] |
  

Here is SQLFiddle demo

如果您在客户端使用 php，那么您可以轻松地放松details使用列到单独的记录中explode()当您迭代结果集时。