这将产生您想要的结果：
select id, ayear, byear, yeardifffrom( select a.id, a.year ayear, b.year byear, (b.year - a.year)+1 yeardiff, dense_rank() over (partition by a.id order by (b.year - a.year) desc) rank from years a join years b on a.id = b.id and b.year > a.year where b.year - a.year = (select count(*)-1 from years a1 where a.id = a1.id and a1.year between a.year and b.year))where rank = 1
编辑 已更新，以显示最长延伸的开始/结束年份。
SQLFiddle
