mysql group_concat通过多个字段进行分组 [英] mysql group_concat group by on multiple fields

问题描述

我有一个表成员，其中member_id，member_name，club_name，region，zone，email作为字段.

I have a Table member with member_id, member_name, club_name, region, zone, email as fields.

我正在使用MySQL group_concat函数，例如

I am using the MySQL group_concat function like

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

这很好.但我希望能够在其他字段上group_concat而无需创建其他查询.

This is working fine. But I would like to be able to group_concat on other fields without creating additional queries.

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

Is it possible to supply the other fields as parameter?

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

**group by club**
[e-mail],[e-mail]
[e-mail]
[e-mail]
[e-mail]

**group by region**
[e-mail], [e-mail], [e-mail], [e-mail]
[e-mail]

**group by zone**
[e-mail], [e-mail], [e-mail]
[e-mail]

说每个区域都有3个区域，每个区域都有一个以上的俱乐部.现在，我该如何获取可以分组或与地区"，区域"或俱乐部"相关的电子邮件?

Say every Region has 3 Zones, every zone has more than one club. Now how can I get emails which can be grouped or related to Region, Zone or Club for that matter?

推荐答案

很难完全根据您的问题来理解您的想法，但是您可以尝试

It's hard to understand what are you after exactly from your question but you can try

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 | [e-mail], [e-mail], [e-mail] | Jhon, Mark, Beth |
|     Club2 |              [e-mail], [e-mail] |    Helen, Thomas |

这里是 SQLFiddle 演示

Here is SQLFiddle demo

附带说明: 在这样的问题中提供示例数据和所需的输出通常可以改善您的更改，从而更快地获得答案，并且最适合您的需求.

更新:如果需要，您可以使用GROUP_CONCAT()使用不同的分隔符来深度包装信息

UPDATE: You can deeply pack information using GROUP_CONCAT() using different separators if it's what you want

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;[e-mail],[e-mail]|B;[e-mail]|C;[e-mail]|D;[e-mail] |
|     region |     1;[e-mail],[e-mail],[e-mail],[e-mail]|2;[e-mail] |
|       zone |     1;[e-mail],[e-mail],[e-mail],[e-mail]|2;[e-mail] |

这里是 SQLFiddle 演示

Here is SQLFiddle demo

如果您在客户端使用php，则可以在遍历结果集的同时使用explode()轻松地将details列展开为单独的记录.

If you're using php on the client side you can then easily enough unwind details column into separate records using explode() while you're iterating over the resultset.

这篇关于mysql group_concat通过多个字段进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！