MYSQL:分组依据和计数问题

Question

我已经研究过这个问题，但找不到解决方案。

如何获得这样的输出？

Answer 1

您可以将原始表连接到一个子查询，该子查询统计每所学校就读的学生。

SELECT t1.student,
       t2.count,
       t1.school
FROM yourTable t1
INNER JOIN
(
    SELECT school, COUNT(*) AS count
    FROM yourTable
    GROUP BY school
) t2
    ON t1.school = t2.school
ORDER BY t2.count DESC,
         t1.school

请注意，问题中并不清楚您的预期顺序。您可能一直在寻找以下顺序:

ORDER BY t1.school,
         t1.count    -- or t2.count DESC