mysql - 检索表中出现次数最多的值

Question

我遇到了一个非常复杂的问题。让我先解释一下我现在在做什么:

我有一个名为 feedback 的表，我在其中存储了针对类(class) ID 的成绩。该表如下所示:

+-------+-------+-------+-------+-----------+--------------
| id    | cid   | grade |g_point| workload  | easiness
+-------+-------+-------+-------+-----------+--------------
| 1     |  10   |  A+   |   1   |      5    |   4
| 2     |  10   |  A+   |   1   |      2    |   4
| 3     |  10   |  B    |   3   |      3    |   3
| 4     |  11   |  B+   |   2   |      2    |   3
| 5     |  11   |  A+   |   1   |      5    |   4
| 6     |  12   |  B    |   3   |      3    |   3
| 7     |  11   |  B+   |   2   |      7    |   8
| 8     |  11   |  A+   |   1   |      1    |   2

g_point 只有特定的成绩值，因此我可以使用这些值来显示按成绩排序的用户类(class)。

好的，现在我的首要任务是打印出每门类(class)的成绩。 成绩 可以根据每门类(class)的最大出现次数来计算。例如，从这个表中我们可以看到 cid = 10 的结果将是 A+，因为它在那里出现了两次。这很简单。我已经实现了这个查询，我将在最后写在这里。

主要问题是当我们谈论有两个不同等级的类(class)cid = 11 时。现在，在那种情况下，客户要求我对这两个类(class)的工作量和难易程度取平均值，应该显示平均水平较高的类(class)。平均值将像这样计算:

  all workload values of the grade against course
+ all easiness values of the grade against course
/ 2 

从这个例子中 cid = 11 有四个条目，对一门类(class)有相同的成绩

B+ 平均成绩

avgworkload(2 + 7)/2=x 
avgeasiness(3 + 8)/2 = y

答案 x+y/2 = 10

A+ 平均成绩

 avgworkload(5 + 1)/2=x 
avgeasiness(4 + 2)/2 = y

  answer x+y/2 = 3

所以成绩应该是B+。

这是我为获得最大出现等级而运行的查询

SELECT
    f3.coursecodeID cid,
    f3.grade_point p,
    f3.grade g
FROM (
    SELECT
        coursecodeID,
        MAX(mode_qty) mode_qty
    FROM (
        SELECT
            coursecodeID,
            COUNT(grade_point) mode_qty
        FROM feedback
        GROUP BY
            coursecodeID, grade_point
    ) f1
    GROUP BY coursecodeID
) f2
INNER JOIN (
    SELECT
        coursecodeID,
        grade_point,
        grade,
        COUNT(grade_point) mode_qty
    FROM feedback
    GROUP BY
        coursecodeID, grade_point
) f3
ON
    f2.coursecodeID = f3.coursecodeID AND
    f2.mode_qty = f3.mode_qty
GROUP BY f3.coursecodeID
ORDER BY f3.grade_point

Answer 1

这里是 SQL Fiddle .我添加了一个包含所有类(class) ID 列表的 Courses 表，以使查询的主要思想更容易看到。您很可能在真实数据库中拥有它。如果没有，您可以通过按 cid 分组，根据 feedback 即时生成它。

对于每个 cid，我们需要找到 grade。按cid, grade 对feedback 进行分组，以获取cid 的所有成绩列表。我们只需要为 cid 选择一个等级，因此我们使用 LIMIT 1。为了确定选择哪个等级，我们订购了它们。首先，按出现次数 - 简单的 COUNT。第二，按平均分。最后，如果有多个等级具有相同的出现和相同的平均分数，则选择具有最小 g_point 的等级。您可以通过调整 ORDER BY 子句来调整规则。

SELECT
  courses.cid
  ,(
    SELECT feedback.grade
    FROM feedback
    WHERE feedback.cid = courses.cid
    GROUP BY
      cid
      ,grade    
    ORDER BY 
      COUNT(*) DESC
      ,(AVG(workload) + AVG(easiness))/2 DESC
      ,g_point
    LIMIT 1
  ) AS CourseGrade
FROM courses
ORDER BY courses.cid

结果集

cid CourseGrade
10  A+
11  B+
12  B

更新

MySQL 没有横向连接，因此获取第二列 g_point 的一种可能方法是重复相关子查询。 SQL Fiddle

SELECT
  courses.cid
  ,(
    SELECT feedback.grade
    FROM feedback
    WHERE feedback.cid = courses.cid
    GROUP BY
      cid
      ,grade    
    ORDER BY 
      COUNT(*) DESC
      ,(AVG(workload) + AVG(easiness))/2 DESC
      ,g_point
    LIMIT 1
  ) AS CourseGrade
  ,(
    SELECT feedback.g_point
    FROM feedback
    WHERE feedback.cid = courses.cid
    GROUP BY
      cid
      ,grade    
    ORDER BY 
      COUNT(*) DESC
      ,(AVG(workload) + AVG(easiness))/2 DESC
      ,g_point
    LIMIT 1
  ) AS CourseGPoint
FROM courses
ORDER BY CourseGPoint

结果集

cid CourseGrade CourseGPoint
10  A+          1
11  B+          2
12  B           3

更新 2 将平均分数添加到 ORDER BY SQL Fiddle

SELECT
  courses.cid
  ,(
    SELECT feedback.grade
    FROM feedback
    WHERE feedback.cid = courses.cid
    GROUP BY
      cid
      ,grade    
    ORDER BY 
      COUNT(*) DESC
      ,(AVG(workload) + AVG(easiness))/2 DESC
      ,g_point
    LIMIT 1
  ) AS CourseGrade
  ,(
    SELECT feedback.g_point
    FROM feedback
    WHERE feedback.cid = courses.cid
    GROUP BY
      cid
      ,grade    
    ORDER BY 
      COUNT(*) DESC
      ,(AVG(workload) + AVG(easiness))/2 DESC
      ,g_point
    LIMIT 1
  ) AS CourseGPoint
  ,(
    SELECT (AVG(workload) + AVG(easiness))/2
    FROM feedback
    WHERE feedback.cid = courses.cid
    GROUP BY
      cid
      ,grade    
    ORDER BY 
      COUNT(*) DESC
      ,(AVG(workload) + AVG(easiness))/2 DESC
      ,g_point
    LIMIT 1
  ) AS AvgScore
FROM courses
ORDER BY CourseGPoint, AvgScore DESC

结果

cid CourseGrade CourseGPoint    AvgScore
10  A+          1               3.75
11  B+          2               5
12  B           3               3