如何按department_id的出现次数排序SQL查询结果集（避免GROUP BY+COUNT组合）

嘿，我完全懂你的需求——要按department_id在结果集中的出现次数降序排列，又不想因为用GROUP BY + COUNT的组合把原本复杂的查询（涉及大量字段和表）搞乱对吧？

这里给你一个完美的解决方案，用窗口函数就能搞定，完全不需要分组聚合：

SELECT 
    s.s_name, 
    p.p_name, 
    p.department_id
FROM students s 
INNER JOIN professors p ON s.professor_id = p.id
ORDER BY 
    COUNT(*) OVER (PARTITION BY p.department_id) DESC,
    p.department_id; -- 可选：如果频次相同，可以按department_id本身排序

为什么这个方法适合你？ #

• 窗口函数COUNT(*) OVER (PARTITION BY p.department_id)会为结果集中的每一行，计算出该行对应的department_id在整个结果里的出现次数，但不会改变原结果集的行数和字段结构，完美适配你实际查询中“大量字段和表”的场景。
• 排序时直接用这个计算出的频次值降序，就能让出现次数多的department_id对应的所有行排在前面，完全符合你给出的示例期望结果。

用你提供的示例数据测试的话，运行这个SQL后，结果会和你期望的完全一致：

如果你需要在频次相同的情况下进一步排序，比如按department_id的字母顺序，只需要在ORDER BY里再加一个字段就行，就像上面代码里注释的那样。

内容的提问来源于stack exchange，提问作者rsp