多说无益,直接上例题
题目:查询各科成绩前三名的记录
按照我一开始的想法:
select * from SC order by CId order by score desc limit 0,3;
通过分组后对成绩进行排序取前3位
显然这是错误的
想要求得结果就需要经过复杂的子循环:
SELECT o.* FROM sc o HAVING
(SELECT COUNT(*) FROM sc WHERE o.CId = CId AND o.score < score) + 1 <= 3
ORDER BY CId, score DESC, SId;
这样又麻烦又难懂
但我们可以直接通过窗户函数实现分组后排序:
select * from (select *, (row_number() over (partition by CId order by score desc)) as rn from SC) as emp where rn < 4;
总结:正常分组后只能通过聚合函数取值,且只取到一个,无法完成位列2及以上的排序,而窗口函数解决分组后无法排序的问题.
具体术式:
窗口函数名(字段名) over([partition by 字段名1(分组)] order by 字段名 [asc | desc](排序)) [as 别
名]