Hier ist der Code, den ich mir ausgedacht habe. Sie könnten dies möglicherweise in eine große Frage stellen, aber ich habe es aus Gründen der Übersichtlichkeit in temporäre Tabellen aufgeteilt, damit Sie es leichter anpassen können. Grundsätzlich benötigen Sie einen mehrstufigen Prozess
- Filtern Sie die Daten
- Gruppieren Sie die Datensätze extern
- Erstellen Sie relationale Gruppen und zählen Sie die Ergebnisse
Code
--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