SELECT user.name, user.subject
FROM user
INNER JOIN (
SELECT name, COUNT(1) AS occurrences
FROM user
GROUP BY name
) AS user_occurrences
ON user.name = user_occurrences.name
ORDER BY user_occurrences.occurrences DESC, user.name ASC, user.subject ASC
LIMIT 4
这可能会更好，具体取决于您使用的RDBMS和数据集的大小。尝试两者并进行比较。
SELECT user.name, user.subject
FROM user
INNER JOIN user AS user_occurrences
ON user.name = user_occurrences.name
GROUP BY user.name --, user.subject Second GROUP BY not needed on MysqL, but it should logically be there
ORDER BY COUNT(user_occurrences.subject) DESC, user.name ASC, user.subject ASC
LIMIT 4
602392714
清零编程群