题目: 一张测试成绩表(test_score_tbl)中包括班级(class), 姓名(name), 分数(score) 三个字段, 求用SQL文检索出每个班级的前三名。
答案: 按照班级(class), 分数(score)排序后,再利用临时变量筛选出前三名:
select @pre_class:=0, @rownum:=0;
select
class, name, score
from
(SELECT class, score, name,
@rownum:= (case when @pre_class = class then @rownum + 1 else 1 end) count,
@pre_class:=class
FROM test.test_score_tbl
order by class asc, score desc ) T
where count < 4;