SQL Server 模式 SQL [英] SQL Server mode SQL

问题描述

我有一张表格，列出了每个班级学生的成绩.我想要一个如下所示的结果集:

I have a table that list students' grades per class. I want a result set that looks like:

BIO...B
CHEM...C

其中B"和C"是班级的模式.我可以获得所有成绩的模式，但不确定如何获得每个班级的模式

Where the "B" and "C" are the modes for the class. I can get a mode of all of the grades, but not sure how to get the mode per class

推荐答案

这里，SQL 2005/2008 上的类似内容:

here, something like this on 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

这篇关于SQL Server 模式 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！