我有一张表格,列出了每个班级学生的成绩。
我希望得到一个结果集,看起来像这样:
BIO...B
CHEM...C
这里的“B”和“C”是指班级的模式。 我可以获得所有成绩的模式,但不确定如何获得每个班级的模式。
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
使用GROUP BY子句。
SELECT className, ClassMode(className)
FROM Grades
GROUP BY className
您的 Mode() 函数当然需要被创建,但它只是一个简单的函数,例如:
CREATE FUNCTION ClassMode(@ClassName varchar(50))
RETURNS varchar(2)
AS
BEGIN
Declare @temp varchar(2)
SELECT @temp = TOP 1 Grade, COUNT(*) Grades as frequency
FROM Grades
WHERE ClassName = @ClassName
GROUP BY ClassName
ORDER BY frequency DESC
RETURN @temp
END