Mysql获取每个班前三名的学生

 

 

 

如图上面是数据：

 

正确解法(假设分数一样则名次一样)

 

SELECT * FROM student t
WHERE (SELECT COUNT(1)+1 FROM student WHERE class_id=t.class_id AND grade>t.grade)<=3

运行结果：

 

 

 

 

 

 

错误解法：来源于这里

SELECT * FROM student t  
WHERE EXISTS(
SELECT COUNT(*) FROM student WHERE grade>=t.grade AND class_id=t.class_id  
GROUP BY class_id  HAVING COUNT(*)<=3
)  
ORDER BY class_id,grade DESC;

运行结果：

 

 

 

 

有趣的是，上面的sql中，grade>=t.grade改为grade>t.grade运行结果好像是2,3,4名？？