这是一个间隙和岛屿风格的问题，但row_numbers()我们在最里面的子查询中使用id和代替了两个row_number()。其次是count() over()获取计数 per grp，最后返回带有 a 的那些cnt > 1。
select id, companyname
from (
select
id
, companyName
, grp
, cnt = count(*) over (partition by companyname, grp)
from (
select *
, grp = id - row_number() over (partition by companyname order by id)
from
companies
) islands
) d
where cnt > 1
order by id
reextester 演示：http ://rextester.com/ACP73683
返回：
+----+-------------+
| id | companyname |
+----+-------------+
| 3 | pigs ltd |
| 4 | pigs ltd |
| 5 | cats ltd |
| 6 | cats ltd |
+----+-------------+
602392714
清零编程群