SQL SQL中的计算模式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13278372/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculate mode in SQL
提问by Frida
I have seen the answer from a previous post, which works fine but I have a small dilemma. Taking the same scenario:
我在之前的帖子中看到了答案，它工作正常，但我有一个小困境。采取相同的场景：
A table that list students' grades per class. I want a result set that looks like:
列出每个班级学生成绩的表格。我想要一个如下所示的结果集：
BIO...B
CHEM...C
Where the "B" and "C" are the modes for the class and want to get the mode for the class.
其中“B”和“C”是班级的模式，并希望获得班级的模式。
Once I applied the below query, i got the following output:
应用以下查询后，我得到以下输出：
Class | Score | Freq | Ranking
2010 | B | 8 | 1
2010 | C | 8 | 1
2011 | A | 10 | 1
2012 | B | 11 | 1
In 2010, I have two grades with the same frequency. What if..I just want to display the highest score, in this case will be "B". How can I achieve that? I would need to assign rankings to the letter grades, but I'm not sure how. Please advise. Thanks.
2010年，我有两个相同频率的年级。如果..我只想显示最高分怎么办，在这种情况下将是“B”。我怎样才能做到这一点？我需要为字母等级分配排名，但我不确定如何。请指教。谢谢。
Prior post: SQL Server mode SQL
上一篇： SQL Server 模式 SQL
The query I used to retrieve the data was the answer from Peter:
我用来检索数据的查询是 Peter 的回答：
;WITH Ranked AS (
SELECT
ClassName, Grade
, GradeFreq = COUNT(*)
, Ranking = DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY COUNT(*) DESC)
FROM Scores
GROUP BY ClassName, Grade
)
SELECT * FROM Ranked WHERE Ranking = 1
回答by RedFilter
Change:
改变：
SELECT * FROM Ranked WHERE Ranking = 1
To:
到：
SELECT Class, MIN(Grade) AS HighestGrade, Freq, Ranking
FROM Ranked
WHERE Ranking = 1
GROUP BY Class, Freq, Ranking
