mysql - 如何避免在同一张 table 上的多个联接？

存在两个表，例如：

GROUPS
id team1_id team2_id
1     1       2
2     3       1

TEAMS
id  name
1   aaa
2   bbb
3   ccc

我需要获取组中的团队名称。因此，查询看起来像这样并且可以工作，但是由于Team表的多个联接，它的查询速度相对较慢。

SELECT t1.name, t2.name
FROM Groups g
JOIN Teams t1 ON t1.id = g.team1_id
JOIN Teams t2 ON t2.id = g.team2_id

是否可以在不更改架构的情况下避免多重连接，或者必须更改架构以改善此情况？每个小组只有2个小组。

最佳答案

您可以看到这样做是否更好-（使用一个连接，但在该表上使用并集）

select x.id, group_concat(t.name order by x.id) as team_names
  from (select id, team1_id as team_id
          from groups
        union all
        select id, team2_id
          from groups) x
  join teams t
    on x.team_id = t.id
 group by x.id

小提琴：http://sqlfiddle.com/#!2/3ba95c/4/0

或者，如果您绝对需要将每个团队放在单独的栏中：

select id,
       substring_index(team_names, ',', -1) as team1,
       substring_index(team_names, ',', 1) as team2
  from (select x.id, group_concat(t.name order by x.id) as team_names
          from (select id, team1_id as team_id
                  from groups
                union all
                select id, team2_id
                  from groups) x
          join teams t
            on x.team_id = t.id
         group by x.id) x

小提琴：http://sqlfiddle.com/#!2/3ba95c/10/0