SELECT JobCodeId, MIN(LastEffectiveDate) AS mindate FROM ( SELECT , prn - rn AS diff FROM ( SELECT , ROW_NUMBER() OVER (PARTITION BY JobCodeID ORDER BY LastEffectiveDate) AS prn, ROW_NUMBER() OVER (ORDER BY LastEffectiveDate) AS rn FROM @tmp ) q ) q2 GROUP BY JobCodeId, diff ORDER BY mindate
连续范围在分区和未分区之间具有相同的区别ROW_NUMBERs
。
您可以在中使用此值GROUP BY
。