sql练习——查询每个班级分数的前3名

下面是 建表语句。

// 建表语句
CREATE TABLE `examination` (
  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `class` varchar(50) DEFAULT NULL COMMENT '班级',
  `student_name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `score` double(4,2) DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

方式一：
– 每个班级分数前3名的名字查出来(没考虑分数相同的情况)

SELECT 		T1.*
FROM 		examination T1
WHERE		(SELECT COUNT(1) FROM examination WHERE T1.class = class AND T1.score < score) < 3
ORDER BY 	class,score DESC

方式二：
– 每个班级分数前3名的名字查出来(考虑分数相同的情况)

	SELECT		T1.*,T2.class as CLASS2,T2.score AS SCORE2,COUNT(*)
	FROM 		examination T1
	LEFT  JOIN	(SELECT DISTINCT class, score FROM examination) T2
	ON 			T1.class = T2.class
 	AND 		T1.score <= T2.score 
	GROUP BY 	student_name,class,score
	HAVING		COUNT(*) < 4
	ORDER BY 	class,score DESC