使用两个多对多时，GROUP_CONCAT中的值重复 [英] Duplicate values in GROUP_CONCAT when using two many-to-many

问题描述

我正在尝试将两个多对多关联添加到一个字符串中.在此示例中，每个团队的颜色数量不确定，赢得奖项的数量也不确定.

这是架构:

这是我正在使用的查询:

SELECT
    teams.name AS name,
    GROUP_CONCAT(colours.name) AS colours,
    GROUP_CONCAT(awards.name) AS awards
FROM
    teams

-- join colours
INNER JOIN teams_to_colours
        ON teams.id = teams_to_colours.team_id

INNER JOIN colours
        ON teams_to_colours.colour_id = colours.id

-- join awards
INNER JOIN teams_to_awards
        ON teams.id = teams_to_awards.team_id

INNER JOIN awards
        ON teams_to_awards.award_id = awards.id

WHERE
    teams.name="A-Team"

GROUP BY
    teams.id

问题是颜色和奖项重复了.假设A-Team的颜色是红色和蓝色，并获得了TrollAward和DarwinAward的奖励……从SQL中获得的结果如下所示:

name: "A-Team"
colours: "red,blue,red,blue"
awards: "TrollAward,DarwinAward,TrollAward,DarwinAward"

我尝试只加入一个多对多的连接，并且效果很好，所以我想我正在通过多个连接来监督某些事情……

解决方案

快速而肮脏的答案是在GROUP_CONCAT()函数中添加DISTINCT:

SELECT
    teams.name AS name,
    GROUP_CONCAT(DISTINCT colours.name) AS colours,
    GROUP_CONCAT(DISTINCT awards.name) AS awards
...

尽管使用大表，这可能不是很有效.第二种方法是在两个子查询(派生表)中访问GROUP BY，在这些子查询中，您从一个中的awards和另一个中的colurs进行串联，然后将这些派生表联接起来.

I'm trying to get in a string two many-to-many associations. In this example, each team has an undetermined number of colours and an undetermined number won awards.

This is the schema:

And this is the query I'm using:

SELECT
    teams.name AS name,
    GROUP_CONCAT(colours.name) AS colours,
    GROUP_CONCAT(awards.name) AS awards
FROM
    teams

-- join colours
INNER JOIN teams_to_colours
        ON teams.id = teams_to_colours.team_id

INNER JOIN colours
        ON teams_to_colours.colour_id = colours.id

-- join awards
INNER JOIN teams_to_awards
        ON teams.id = teams_to_awards.team_id

INNER JOIN awards
        ON teams_to_awards.award_id = awards.id

WHERE
    teams.name="A-Team"

GROUP BY
    teams.id

The problem is that the colours and the awards get duplicated. Let's say A-Team has red and blue as colours, and as awards TrollAward and DarwinAward... the results I get from the SQL look like this:

name: "A-Team"
colours: "red,blue,red,blue"
awards: "TrollAward,DarwinAward,TrollAward,DarwinAward"

I have tried to join only one many-to-many, and works perfectly, so I guess I'm overseeing something with the multiple joins...

解决方案

The fast and dirty answer is to add DISTINCT in the GROUP_CONCAT() functions:

SELECT
    teams.name AS name,
    GROUP_CONCAT(DISTINCT colours.name) AS colours,
    GROUP_CONCAT(DISTINCT awards.name) AS awards
...

This may not be very efficient though with big tables. The second approach is to GROUP BY in two subqueries (derived tables), where you get the concatenation from awards in one and from colurs in the other, and then join these derived tables.

这篇关于使用两个多对多时，GROUP_CONCAT中的值重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！