获取其计数与类别的最大(计数)匹配的记录

假设gradeCount已经是每个唯一课程、节和年级的总成绩。

首先找出每个年级的最高分数

SELECT
    grade,
    Max(gradeCount) as MaxGradeCount
FROM
    table

然后查找原始表中哪些行具有最大坡度

SELECT
    course,
    section,
    grade,
    gradeCount
FROM
    table

        INNER JOIN
    (SELECT
        grade,
        Max(gradeCount) as MaxGradeCount
    FROM
        table
    ) MaxGrades
        ON  table.grade = MaxGrades.grade
            AND table.gradeCount = MaxGrades.MaxGradeCount
ORDER BY 
    table.grade

一个简单的内部连接,看不到CTE;-)

假设 CTE :

declare @Test table (
    course char(4),
    section char(3),
    grade char(2),
    gradeCount int
)

insert into @Test
    values ('1301','001','A',100),
           ('1301','002','A',20),
           ('1301','001','B',10),
           ('1301','002','B',50),
           ('1301','003','B',50)

;with cteMaxGradeCount as (
    select grade, max(gradeCount) as MaxGradeCount
        from @Test
        group by grade
)
select t.course, t.SECTION, t.grade, t.gradeCount
    from cteMaxGradeCount c
        inner join @Test t
            on c.grade = t.grade
                and c.MaxGradeCount = t.gradeCount
    order by t.grade

not exists 要仅筛选出计数最高的分数:

; with s as 
    (
    select  course
    ,       section
    ,       left(grade,1) as Grade
    ,       sum(gradeCount) sumGradeCount
    from    YourTable yt1
    group by
            course
    ,       section
    ,       left(grade,1)
    )
select  *
from    s s1
where   not exists
        (
        select  *
        from    s s2
        where   s1.course = s2.course
                and s1.section = s2.section
                and s1.sumGradeCount < s2.SumGradeCount
        )

可以将GroupBy与聚合函数-max()、count()结合使用。