问题描述
我正在尝试将两个多对多关联添加到一个字符串中.在此示例中,每个团队的颜色数量不确定,赢得奖项的数量也不确定.
这是架构:
这是我正在使用的查询:
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中的值重复的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!