我有一张表格,列出了每个班级的学生成绩。
我想要一个如下所示的结果集:
BIO...B
CHEM...C
其中“B”和“C”是该类的模式。
我可以获得所有成绩的模式,但不确定如何获得每个班级的模式
这里,在 SQL 2005/2008 上是这样的:
;WITH
Counts AS (
SELECT ClassName, Grade, COUNT(*) AS GradeFreq
FROM Scores
GROUP BY ClassName, Grade
)
, Ranked AS (
SELECT ClassName, Grade, GradeFreq
, Ranking = DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY GradeFreq DESC)
FROM Counts
)
SELECT * FROM Ranked WHERE Ranking = 1
或者也许只是:
;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
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)