检查此查询。内部查询组重新定位历史以获取至少有两个或多个重新定位的公司id。

select * from companies 
where id in (
  select company_id 
  from company_relocation_histories 
  group by company_id
  having count(*) = 2
);

编辑：根据op的评论

SELECT * 
FROM   companies 
WHERE  id IN (SELECT company_id 
              FROM   (SELECT company_id, 
                             Lead(relocation_location) OVER(ORDER BY id) x, 
                             Count(*) OVER (partition BY company_id) cnt 
                      FROM   company_relocation_histories) t 
              WHERE  x = 'FR' 
                     AND cnt = 2 
              GROUP  BY company_id);

您可以使用max（id）和count>1的子查询

SELECT * 
FROM   `companies`  c
INNER JOIN company_relocation_histories h on c.id = h.company_id
INNER JOIN 
(
    SELECT company_id, Max(id)  max_id
    FROM   `company_relocation_histories` AS `sub` 
    group by company_id 
    having count(*) >1
) t on t.company_id = c.id 
        and t.max_id  = h.id 
          and h.relocation_location  ='FR'

这样你就有了所有的公司与多个地点和相关的最后一个地点