SQL 面试题：获取每个班级前10名同学成绩

题目: 获取每个班级前10名同学成绩

-- 方式一 ,

select DISTINCT s.* from test  s ,(SELECT SUBSTRING_INDEX(GROUP_CONCAT(name ORDER BY grade DESC),",",10) as names from test s GROUP BY class) d 
WHERE FIND_IN_SET(s.name,d.names) 
	BETWEEN 0 AND 10 
	ORDER BY class,grade desc;	

-- 方式二
-- where 子语句查询 , 将成绩和班级与原表关联,

SELECT * from test as a WHERE  (SELECT count(*) from test WHERE  class = a.class and grade = a.grade) < 10 ORDER BY a.class,a.grade desc;