如何使用 SQL Server 2008 将学生分数分组为五分位数

问题描述

谁能帮我把学生的分数分成五分?我认为 SQL Server 2012 中有一个功能，但我们仍然没有升级到它，因为我们使用的是 2008R2.我尝试了Ntile(5)`，但它没有产生预期的结果.我需要在 Quintile 列下方

Can anyone help me to group student scores into quintile? I think there is a feature in SQL Server 2012, but still we havent upgraded to it as we are using 2008R2. I triedNtile(5)` but it is not generating the desired result. I need below Quintile column

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``

这篇关于如何使用 SQL Server 2008 将学生分数分组为五分位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！