mysql - 这个SQL语句怎么写呢？

Question

下表如下:用户

Name:  Subject:
Peter   Math
Mary    Chinese
Mary    Computer
Mary    Hist
Mary    PE
Mary    English
Peter   Art
Chris   English
Chris   Computer
Peter   Computer
Paul    Math

我想得到名字中出现的前4个结果，并且返回前4个结果应该是主题名称。例如，在本例中，顶部出现的名字是 Mary，并且根据主题中的顺序，中文、计算机、英语，所以我希望得到结果:

Mary    Chinese
Mary    Computer
Mary    English
Mary    Hist

如果玛丽不是最足以显示结果的，那么第二个人将是下面的，就像，假设表格将是这样的:

Name:  Subject:
Peter   Math
Mary    Chinese
Mary    Computer
Mary    Hist
Peter   Art
Chris   English
Chris   Computer
Peter   Computer
Paul    Math

结果将是，

Mary    Chinese
Mary    Computer
Mary    Hist
Peter   Art

因为Mary出现的次数最多，所以Mary会回来，但是Mary不足以填补4个位置，所以出现次数第二多的将取代这个位置，在这种情况下，我们使用Peter。

Answer 1

SELECT user.name, user.subjectFROM userINNER JOIN (    SELECT name, COUNT(1) AS occurrences    FROM user    GROUP BY name  ) AS user_occurrences  ON user.name = user_occurrences.nameORDER BY user_occurrences.occurrences DESC, user.name ASC, user.subject ASCLIMIT 4

编辑这可能会表现更好，具体取决于您使用的 RDBMS 和数据集的大小。尝试两者并进行比较。

SELECT user.name, user.subjectFROM userINNER JOIN user AS user_occurrences  ON user.name = user_occurrences.nameGROUP BY user.name --, user.subject Second GROUP BY not needed on MySQL, but it should logically be thereORDER BY COUNT(user_occurrences.subject) DESC, user.name ASC, user.subject ASCLIMIT 4