表格：

// ================提示：建表语句在文章末尾================= 
// ================提示：建表语句在文章末尾================= 
// ================提示：建表语句在文章末尾================= 
Student(s_id,s_no,s_name)，学生表：学生id、学生编号、学生姓名 
Course(c_id,c_no,c_name)，课程表：课程id、课程编号、课程名称 
Grade(c_id,s_id,score) ，成绩表：课程id、学生id、成绩 

问题：

1、用sql语句查询每科成绩前3名的学生编号、学生姓名、课程编号、成绩

SELECT 
	c.c_no, 
	s.s_no, 
	s.s_name, 
	a.score  
FROM 
	grade a 
	JOIN student s ON a.s_id = s.s_id 
	JOIN course c ON a.c_id = c.c_id  
WHERE 
	a.score >= ( SELECT DISTINCT b.score FROM grade b WHERE a.c_id = b.c_id ORDER BY b.score DESC LIMIT 2, 1 )  
ORDER BY 
	a.c_id, 
	a.score DESC; 

2、用sql语句查询每科成绩第3名的学生编号、学生姓名、课程编号、成绩

SELECT 
	c.c_no, 
	s.s_no, 
	s.s_name, 
	a.score  
FROM 
	grade a 
	JOIN student s ON a.s_id = s.s_id 
	JOIN course c ON a.c_id = c.c_id  
WHERE 
	a.score = ( SELECT DISTINCT b.score FROM grade b WHERE a.c_id = b.c_id ORDER BY b.score DESC LIMIT 2, 1 )  
ORDER BY 
	a.c_id, 
	a.score DESC; 

3、用sql语句查询每科成绩第1名的学生编号、学生姓名、课程编号、成绩

SELECT 
	c.c_no, 
	s.s_no, 
	s.s_name, 
	b.maxScore  
FROM 
	grade a 
	JOIN student s ON a.s_id = s.s_id 
	JOIN course c ON a.c_id = c.c_id 
	JOIN ( SELECT c_id, max( score ) maxScore FROM grade GROUP BY c_id ) b ON a.c_id = b.c_id  
	AND a.score = b.maxScore  
ORDER BY 
	a.c_id, 
	b.maxScore DESC 

解释：

如果查询每班每科成绩的前三名/第三名/第一名，我们只需要在子查询中的where中加上一个相等条件就可以了，所以不用担心这些问题，然后你要好好看看sql，也不用担心出现重复分数的情况，因为上面的sql语句已经解决了该问题，由于毕竟每次都需要进行子查询，所以sql效率还是太低，等到我发现了更好办法的时候在更新吧！

建表语句：

DROP TABLE IF EXISTS `grade`; 
DROP TABLE IF EXISTS `course`; 
DROP TABLE IF EXISTS `student`; 
CREATE TABLE `course` ( 
  `c_id` int(11) NOT NULL AUTO_INCREMENT, 
  `c_no` varchar(255) DEFAULT NULL, 
  `c_name` varchar(255) DEFAULT NULL, 
  PRIMARY KEY (`c_id`) 
); 
CREATE TABLE `student` ( 
  `s_id` int(11) NOT NULL AUTO_INCREMENT, 
  `s_no` int(11) DEFAULT NULL, 
  `s_name` varchar(255) DEFAULT NULL, 
  PRIMARY KEY (`s_id`) 
); 
CREATE TABLE `grade` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `c_id` int(11) DEFAULT NULL, 
  `s_id` int(11) DEFAULT NULL, 
  `score` int(11) DEFAULT NULL, 
  PRIMARY KEY (`id`), 
  KEY `s_id` (`s_id`), 
  KEY `c_id` (`c_id`), 
  CONSTRAINT `grade_ibfk_1` FOREIGN KEY (`s_id`) REFERENCES `student` (`s_id`), 
  CONSTRAINT `grade_ibfk_2` FOREIGN KEY (`c_id`) REFERENCES `course` (`c_id`) 
); 
INSERT INTO `course` VALUES (1, '1901', '数学'); 
INSERT INTO `course` VALUES (2, '1902', '英语'); 
INSERT INTO `student` VALUES (1, 1001, '刘一'); 
INSERT INTO `student` VALUES (2, 1002, '陈二'); 
INSERT INTO `student` VALUES (3, 1003, '张三'); 
INSERT INTO `student` VALUES (4, 1004, '李四'); 
INSERT INTO `student` VALUES (5, 1005, '王五'); 
INSERT INTO `student` VALUES (6, 1006, '赵六'); 
INSERT INTO `grade` VALUES (1, 1, 1, 90); 
INSERT INTO `grade` VALUES (2, 1, 2, 90); 
INSERT INTO `grade` VALUES (3, 1, 3, 78); 
INSERT INTO `grade` VALUES (4, 1, 4, 60); 
INSERT INTO `grade` VALUES (5, 1, 5, 88); 
INSERT INTO `grade` VALUES (6, 1, 6, 77); 
INSERT INTO `grade` VALUES (7, 2, 1, 99); 
INSERT INTO `grade` VALUES (8, 2, 2, 89); 
INSERT INTO `grade` VALUES (9, 2, 3, 67); 
INSERT INTO `grade` VALUES (10, 2, 4, 50); 
INSERT INTO `grade` VALUES (11, 2, 5, 80); 
INSERT INTO `grade` VALUES (12, 2, 6, 80);