避免包含group by的嵌套子查询

Question

有没有更好的方法在不使用嵌套子查询的情况下执行此查询？

-- select all races for jockeys which have multiple regions
select distinct r.id, r.description
from jockeys_races jra inner join races r on r.id = jra.race_id
where jra.jockey_id in (
    select jre.jockey_id
    from jockeys_regions jre
    group by jre.jockey_id
    having count(*) > 1
);

骑师可以属于多个区域。骑师可以参加多场比赛。

Answer 1

它可能不是那么有效(因为产品会更大)，但你可以做到。检查两个文件上的解释。

select distinct r.id, r.description
from races r
  inner join jockey_races jra on r.id = jra.race_id
  inner join jockey_regions jre on jra.jockey_id = jre.jockey_id
group by r.id, r.description, jre.jockey_id
having count(*) > 1

Answer 2

我相信你能做到的：

select distinct r.id, r.description
from jockeys_races jra 
inner join races r on r.id = jra.race_id
inner join jockeys_regions jre on jre.jockey_id=jra.jockey_id
group by r.id, r.description, jre.jockey_id
having count(jre.region) > 1

Answer 3

您不希望对MySQL中的子查询使用IN。。。优化器在这个构造上做得非常糟糕。在这种情况下，您可以将子查询移动到FROM子句：

select distinct r.id, r.description
from jockeys_races jra inner join
     races r
     on r.id = jra.race_id join
     (select jre.jockey_id
      from jockeys_regions jre
      group by jre.jockey_id
      having count(*) > 1
     ) ji
     on jra.jockey_id = ji.jockey_id