ew。这是一个真正的脑筋急转弯。首先，我用于测试的表架构为：
Create Table Scores ( Id int not null identity(1,1) primary key clustered , [Date] datetime not null , Score int not null)
现在，首先，我在SQL 2008中使用CTE计算值以检查答案，然后构建了一个可以在SQL 2000中使用的解决方案。因此，在SQL
2008中，我们执行以下操作：
;With SummaryStatistics As ( Select Year([Date]) As YearNum , Month([Date]) As MonthNum , Min(Score) As MinScore , Max(Score) As MaxScore , Avg(Score) As AvgScore From Scores Group By Month([Date]), Year([Date]) ) , Percentiles As ( Select Year([Date]) As YearNum , Month([Date]) As MonthNum , Score , NTile( 100 ) Over ( Partition By Month([Date]), Year([Date]) Order By Score ) As Percentile From Scores ) , ReportedPercentiles As ( Select YearNum, MonthNum , Min(Case When Percentile = 45 Then Score End) As Percentile45 , Min(Case When Percentile = 55 Then Score End) As Percentile55 From Percentiles Where Percentile In(45,55) Group By YearNum, MonthNum )Select SS.YearNum, SS.MonthNum , SS.MinScore, SS.MaxScore, SS.AvgScore , RP.Percentile45, RP.Percentile55From SummaryStatistics As SS Join ReportedPercentiles As RP On RP.YearNum = SS.YearNum And RP.MonthNum = SS.MonthNumOrder By SS.YearNum, SS.MonthNum
现在是一个SQL 2000解决方案。本质上，诀窍是使用几个临时表来计算分数的出现。
If object_id('tempdb..#Working') is not null DROp TABLE #WorkingGOCreate Table #Working ( YearNum int not null , MonthNum int not null , Score int not null , Occurances int not null , Constraint PK_#Working Primary Key Clustered ( MonthNum, YearNum, Score ) )GOInsert #Working(MonthNum, YearNum, Score, Occurances)Select Month([Date]), Year([Date]), Score, Count(*)From ScoresGroup By Month([Date]), Year([Date]), ScoreGOIf object_id('tempdb..#SummaryStatistics') is not null DROp TABLE #SummaryStatisticsGOCreate Table #SummaryStatistics ( MonthNum int not null , YearNum int not null , Score int not null , Occurances int not null , CumulativeTotal int not null , Percentile float null , Constraint PK_#SummaryStatistics Primary Key Clustered ( MonthNum, YearNum, Score ) )GOInsert #SummaryStatistics(YearNum, MonthNum, Score, Occurances, CumulativeTotal)Select W2.YearNum, W2.MonthNum, W2.Score, W2.Occurances, Sum(W1.Occurances)-W2.OccurancesFrom #Working As W1 Join #Working As W2 On W2.YearNum = W1.YearNum And W2.MonthNum = W1.MonthNumWhere W1.Score <= W2.ScoreGroup By W2.YearNum, W2.MonthNum, W2.Score, W2.OccurancesUpdate #SummaryStatisticsSet Percentile = SS.CumulativeTotal * 100.0 / MonthTotal.TotalFrom #SummaryStatistics As SS Join ( Select SS1.YearNum, SS1.MonthNum, Max(SS1.CumulativeTotal) As Total From #SummaryStatistics As SS1 Group By SS1.YearNum, SS1.MonthNum ) As MonthTotal On MonthTotal.YearNum = SS.YearNum And MonthTotal.MonthNum = SS.MonthNumSelect GeneralStats.*, Percentiles.Percentile45, Percentiles.Percentile55From ( Select Year(S1.[Date]) As YearNum , Month(S1.[Date]) As MonthNum , Min(S1.Score) As MinScore , Max(S1.Score) As MaxScore , Avg(S1.Score) As AvgScore From Scores As S1 Group By Month(S1.[Date]), Year(S1.[Date]) ) As GeneralStats Join ( Select SS1.YearNum, SS1.MonthNum , Min(Case When SS1.Percentile >= 45 Then Score End) As Percentile45 , Min(Case When SS1.Percentile >= 55 Then Score End) As Percentile55 From #SummaryStatistics As SS1 Group By SS1.YearNum, SS1.MonthNum ) As Percentiles On Percentiles.YearNum = GeneralStats.YearNum And Percentiles.MonthNum = GeneralStats.MonthNum
