Вот код, который я придумал. Вы могли бы потенциально поместить это в один большой запрос, но я разбил его на временные таблицы для ясности, чтобы вы могли легче адаптировать его. В основном вам потребуется многошаговый процесс
- Фильтровать данные
- Внешне сгруппировать записи
- Создавайте реляционные группы и подсчитывайте результаты
Код
--Test data create table #testRank ( RowNum int identity, Name nvarchar(255), Score int ) insert #testRank values ('Jim',7), ('Jim',4), ('Jim',4), ('Jim',7), ('Jim',7), ('Jim',7), ('Jim',7), ('Jim',4), ('Ted',4), ('Ted',4), ('Ted',7), ('Ted',7) -- I've added a second set of Jim data for testing, ('Jim',7), ('Jim',7), ('Jim',7), ('Jim',7) --minimize the dataset; if your dealing with millions of rows, filter out the scores less than 7 select RowNum, Name into #smallerDataSet from #testRank where score = 7; --create groups or strings delineated by NULL values indicating the start and end of the string of scores about 7 select x.RowNum, x.Name, Case when z.NextRow is null then null else x.RowNum-1 end PrevRow, Case when y.PrevRow is null then null else x.RowNum+1 end NextRow into #strings from #smallerDataSet x Left Join (select RowNum-1 PrevRow, Name from #smallerDataSet) y on y.PrevRow = x.RowNum and y.Name = x.Name Left Join (select RowNum+1 NextRow, Name from #smallerDataSet) z on z.NextRow = x.RowNum and z.Name = x.Name Where PrevRow is not null or NextRow is not null --visulaize the query above select * from #strings --use the ROW_NUMBER function to seperate the groups from each other; particularly important when the same name has the same string count --then simply do the math and order the list Select Distinct p.Name, EndStr-StartStr+1 MaxString from ( select ROW_NUMBER() over (order by RowNum) GroupNum, Name, RowNum StartStr from #strings where PrevRow is null ) p join ( select ROW_NUMBER() over (order by RowNum) GroupNum, Name, RowNum EndStr from #strings where NextRow is null ) n on n.GroupNum = p.GroupNum and n.Name = p.Name order by MaxString desc drop table #strings drop table #smallerDataSet drop table #testRank