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
