mysql 分组 concat_mysql group_concat通过多个字段分组

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 email1@example.com

2 member2 A 1 1 email2@example.com

3 member3 B 1 1 email3@example.com

4 member4 C 1 2 email4@example.com

5 member5 D 2 1 email5@example.com

**group by club**

email1@example.com,email2@example.com

email3@example.com

email4@example.com

email5@example.com

**group by region**

email1@example.com, email2@example.com, email3@example.com, email4@example.com

email5@example.com

**group by zone**

email1@example.com, email2@example.com, email3@example.com

email5@example.com

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

Sample output:

| CLUB_NAME | EMAILS | MEMBERS |

------------------------------------------------------------------------

| Club1 | m1@mail.com, m2@mail.com, m3@mail.com | Jhon, Mark, Beth |

| Club2 | m4@mail.com, m5@mail.com | Helen, Thomas |

Here is

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;email1@example.com,email2@example.com|B;email3@example.com|C;email4@example.com|D;email5@example.com |

| region | 1;email1@example.com,email2@example.com,email3@example.com,email4@example.com|2;email5@example.com |

| zone | 1;email1@example.com,email2@example.com,email3@example.com,email5@example.com|2;email4@example.com |

Here is

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.