谁能帮助我将学生成绩分为五等分？我认为SQL Server 2012中有一项功能，但我们仍然没有t upgraded to it as we are using 2008R2. I triedNtile（5）`，但未产生预期的结果。我需要在昆泰栏下面
t upgraded to it as we are using 2008R2. I tried
Student Score Quintile ------------------------ Student1 20 1 Student2 20 1 Student3 30 2 Student4 30 2 Student5 40 2 Student6 40 2 Student7 50 3 Student8 50 3 Student9 60 3 Student10 70 4 Student11 70 4 Student12 80 4 Student13 80 4 Student14 90 5
Below is the correct answer given by Erland Sommarskog Create Table #Scores(Student varchar(20), Score int); Insert #Scores(Student, Score) Values ('Student1', 20) ,('Student2', 20) ,('Student3', 30) ,('Student4', 30) ,('Student4', 30) ,('Student4', 30) ,('Student5', 40) ,('Student6', 40) ,('Student7', 50) ,('Student8', 50) ,('Student9', 60) ,('Student10', 70) ,('Student11', 70) ,('Student12', 80) ,('Student13', 80) ,('Student14', 90); ; WITH quintiles AS ( SELECT Score, ntile(5) OVER(ORDER BY Score) AS quintile FROM (SELECT DISTINCT Score FROM #Scores) AS s ) SELECT s.Student, s.Score, q.quintile FROM #Scores s JOIN quintiles q ON s.Score = q.Score go DROP TABLE #Scores --by Erland Sommarskog``
