SQL语句不会写，请大神指教！

[u010394312]

student (s_id, sanme, sage, ssex) 学生表
course (c_id, cname, t_id) 课程表
sc (s_id, c_id, score) 成绩表
teacher (t_id, tname) 教师表

1、查询每门课程成绩最好的前两名
2、查询没学过“叶平”老师课的同学的学号、姓名

会写这两个SQL语句的请指教一下，感激不尽！

附录：建表语句以及数据

CREATE TABLE `course` (
`c_id` int(10) NOT NULL,
`cname` varchar(20) DEFAULT NULL,
`t_id` int(10) DEFAULT NULL,
PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------
INSERT INTO `course` VALUES ('1', '语文', '101');
INSERT INTO `course` VALUES ('2', '数学', '102');
INSERT INTO `course` VALUES ('3', '英语', '103');
INSERT INTO `course` VALUES ('4', '物理', '104');
INSERT INTO `course` VALUES ('5', '化学', '104');
INSERT INTO `course` VALUES ('6', '历史', '105');
INSERT INTO `course` VALUES ('7', '政治', '105');
INSERT INTO `course` VALUES ('8', '地理', '106');
INSERT INTO `course` VALUES ('9', '体育', '101');
INSERT INTO `course` VALUES ('10', '生物', '102');
INSERT INTO `course` VALUES ('11', '美术', '106');



CREATE TABLE `student` (
`s_id` int(10) NOT NULL,
`sanme` varchar(20) DEFAULT NULL,
`sage` int(3) DEFAULT NULL,
`ssex` varchar(1) DEFAULT NULL,
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `student` VALUES ('11', '张三', '16', '男');
INSERT INTO `student` VALUES ('12', '李四', '17', '男');
INSERT INTO `student` VALUES ('13', '张扬', '16', '男');
INSERT INTO `student` VALUES ('14', '张燕', '16', '女');
INSERT INTO `student` VALUES ('15', '王芳', '16', '女');
INSERT INTO `student` VALUES ('16', '赵六', '17', '男');


CREATE TABLE `teacher` (
`t_id` int(10) NOT NULL,
`tname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('101', '张华');
INSERT INTO `teacher` VALUES ('102', '李四');
INSERT INTO `teacher` VALUES ('103', '叶平');
INSERT INTO `teacher` VALUES ('104', '叶平');
INSERT INTO `teacher` VALUES ('105', '黄英');
INSERT INTO `teacher` VALUES ('106', '姜红');



CREATE TABLE `sc` (
`s_id` int(10) DEFAULT NULL,
`c_id` int(10) DEFAULT NULL,
`score` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `sc` VALUES ('11', '1', '80');
INSERT INTO `sc` VALUES ('11', '2', '77');
INSERT INTO `sc` VALUES ('11', '3', '88');
INSERT INTO `sc` VALUES ('11', '4', '67');
INSERT INTO `sc` VALUES ('11', '5', '67');
INSERT INTO `sc` VALUES ('11', '6', '68');
INSERT INTO `sc` VALUES ('11', '7', '95');
INSERT INTO `sc` VALUES ('11', '8', '93');
INSERT INTO `sc` VALUES ('11', '9', '57');
INSERT INTO `sc` VALUES ('11', '10', '89');
INSERT INTO `sc` VALUES ('12', '1', '56');
INSERT INTO `sc` VALUES ('12', '2', '78');
INSERT INTO `sc` VALUES ('12', '4', '79');
INSERT INTO `sc` VALUES ('12', '7', '23');
INSERT INTO `sc` VALUES ('12', '9', '86');
INSERT INTO `sc` VALUES ('13', '1', '46');
INSERT INTO `sc` VALUES ('13', '2', '79');
INSERT INTO `sc` VALUES ('13', '3', '88');
INSERT INTO `sc` VALUES ('13', '5', '88');
INSERT INTO `sc` VALUES ('14', '1', '67');
INSERT INTO `sc` VALUES ('14', '4', '77');
INSERT INTO `sc` VALUES ('15', '1', '86');
INSERT INTO `sc` VALUES ('15', '3', '78');

[u010394312]

大神们，确定好用后请回复一下！

[李皮皮]

1.
select sc.s_id, score, name
from sc inner join student
where sc.s_id = student.s_id
order by score desc limit 2;

2.
select s_id, sname from student
where s_id not in
(
select s_id from sc inner join teacher inner join course
on sc.c_id = course.c_id
and teacher.t_id = course.t_id
where tname = ‘叶平’
);

如果觉得有用，记得给分呦

[楓VS痕]

SELECT r1.score, s.sanme, c.cname FROM sc r1
LEFT JOIN student s ON s.s_id = r1.s_id
LEFT JOIN course c ON c.c_id = r1.c_id
WHERE (SELECT count(1) FROM sc r2 WHERE r2.c_id=r1.c_id AND r2.score >= r1.score) <= 2
ORDER BY r1.c_id, r1.score DESC;


SELECT s.* FROM student s
WHERE s.s_id NOT IN(
SELECT sc.s_id FROM sc
LEFT JOIN course c ON c.c_id = sc.c_id
LEFT JOIN teacher t ON c.t_id = t.t_id
WHERE t.tname='叶平'
GROUP BY sc.s_id
)

[u010394312]

引用 3 楼 xiewu91124 的回复:

SELECT r1.score, s.sanme, c.cname FROM sc r1
LEFT JOIN student s ON s.s_id = r1.s_id
LEFT JOIN course c ON c.c_id = r1.c_id
WHERE (SELECT count(1) FROM sc r2 WHERE r2.c_id=r1.c_id AND r2.score >= r1.score) <= 2
ORDER BY r1.c_id, r1.score DESC;


SELECT s.* FROM student s
WHERE s.s_id NOT IN(
SELECT sc.s_id FROM sc
LEFT JOIN course c ON c.c_id = sc.c_id
LEFT JOIN teacher t ON c.t_id = t.t_id
WHERE t.tname='叶平'
GROUP BY sc.s_id
)

高手厉害，两题试了都是目标结果！结贴给分！

[u010394312]

引用 2 楼 xmcx_328 的回复:

1.
select sc.s_id, score, name
from sc inner join student
where sc.s_id = student.s_id
order by score desc limit 2;

2.
select s_id, sname from student
where s_id not in
(
select s_id from sc inner join teacher inner join course
on sc.c_id = course.c_id
and teacher.t_id = course.t_id
where tname = ‘叶平’
);

如果觉得有用，记得给分呦

第一题答得不对，目标是所有科目的前两名，第二题没考虑到几张表是外连接的关联关系。