查找表中每个ID的最大连续年份（Oracle SQL）

查找表中每个ID的最大连续年份（Oracle SQL）

这将产生您想要的结果：

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