mysql group_concat group by on multiple fields

问题:

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

I am using the MySQL group_concat function like

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

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]

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?

回答1:

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

Sample output:

| CLUB_NAME |                                EMAILS |          MEMBERS |
------------------------------------------------------------------------
|     Club1 | [e-mail], [e-mail], [e-mail] | Jhon, Mark, Beth |
|     Club2 |              [e-mail], [e-mail] |    Helen, Thomas |

Here is SQLFiddle demo

On a side note: providing sample data and desired output in a question like this usually improves your changes of getting your answer faster and that best fits your needs.

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

Sample output:

| 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] |

Here is SQLFiddle demo

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.