获取其计数与类别的max（count）相匹配的记录 [英] Get records whose count matches max(count) for a category

问题描述

给出以下课程行，节，年级，课程节中的成绩计数：

Given the following rows of course,section,grade,count of grades within course section:

course  SECTION  grade  gradeCount
-----------------------------------
1301    001      C      3
1301    001      C+     3
1301    001      C-     4
1301    001      D      5
1301    001      D+     3
1301    001      D-     2
1301    001      F      18
1301    002      A-     1
1301    002      B      1
1301    002      B-     3
1301    002      C      2

我想获取每个年级最多的课程/科目列表。

I want to get a list of course/sections with the greatest number of each grade.

例如：

Grade|Course|Section|Count
A | 1301| 023 | 75     // 1301-023 had the most A's, 75 of them
B | 1301| 033 | 65     // 1301-033 had the most B's, 65 of them

领带应该出现在列表中。

Ties should appear in the list.

推荐答案

假定gradeCount已经是每个唯一课程，科目和年级的总成绩。

Assuming the gradeCount is already the total of the grades for each unique course, section and grade.

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

First find the highest count for each grade

SELECT
    grade,
    Max(gradeCount) as MaxGradeCount
FROM
    table

然后找到哪个原始表中的行具有最高评分

Then find which lines in the original table have the max grade

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 ;-)

A simple inner join and no CTEs in sight ;-)

这篇关于获取其计数与类别的max（count）相匹配的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！