Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
996 views
in Technique[技术] by (71.8m points)

sql server - Sql query to Count Total Consecutive Years from latest year

I have a table Temp:

 CREATE TABLE Temp 
( 
  [ID]  [int],
  [Year]  [INT],
 )
**ID    Year**
1 2016
1   2016
1   2015
1   2012
1   2011
1   2010
2   2016
2   2015
2   2014
2   2012
2   2011
2   2010
2   2009
3   2016
3   2015
3   2004
3   1999
4   2016
4   2015
4   2014
4   2010
5   2016
5   2014
5   2013

I want to calculate the total consecutive years starting from the most recent Year. Result should look like this:

ID  Total Consecutive Yrs
1   2
2   3
3   2
4   3
5   1
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

You can use lead and get this counts as below:

Select top (1) with ties Id, RowN as [Total Consecutive Years] from (
    Select *, Num = case when ([year]- lead(year) over(partition by Id order by [Year] desc) > 1) then 0 else 1 end 
        , RowN = Row_Number() over (partition by Id order by [Year] desc)
    from temp
) a
where a.Num = 0
order by row_number() over(partition by Id order by RowN)

Output as below:

+----+-------------------------+
| Id | Total Consecutive Years |
+----+-------------------------+
|  1 |                       2 |
|  2 |                       3 |
|  3 |                       2 |
|  4 |                       3 |
|  5 |                       1 |
+----+-------------------------+

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...