MYSQL - 如何构建此查询？

Question

我想获得一个群组列表及其成员(只显示一次)，只给出一个成员名称:

表_a:

| GROUP_ID |   NAME |
---------------------
|        1 |    Tom |
|        2 |  Frank |
|        3 | Shilla |
|        1 | Scully |
|        3 | Scully |
|        3 | Scully |
|        3 | Scully |
|        4 | Scully |
|        1 |    Jen |

表_b:

| ID | GROUP_NAME |
-------------------
|  1 |   Troopers |
|  2 |      Clubs |
|  3 |  Mavericks |
|  4 |   Tomatoes |

这是 SQLFidlle
这是结果:

| GROUP_NAME |        GROUP_CONCAT(C.NAME) |
--------------------------------------------
|  Mavericks | Scully,Scully,Scully,Shilla |
|   Tomatoes |                      Scully |
|   Troopers |              Scully,Jen,Tom |

我想得到的是

| GROUP_NAME | GROUP_CONCAT(C.NAME) |
-------------------------------------
|  Mavericks | Scully,Shilla        |
|   Tomatoes | Scully               |
|   Troopers | Scully,Jen,Tom       |

我应该如何更改查询？

Answer 1

只需添加DISTINCT

SELECT  b.GROUP_NAME, GROUP_CONCAT(DISTINCT c.Name)
FROM    
        (
            SELECT GROUP_ID
            FROM   table_a
            WHERE  name = 'Scully'
            GROUP BY table_a.GROUP_ID
        ) a 
        INNER JOIN table_b b
            ON a.GROUP_ID = b.ID
        INNER JOIN table_a c
            ON a.GROUP_ID = c.GROUP_ID
GROUP BY b.GROUP_NAME;

• SQLFiddle Demo