题目:现有
学生表 student:s_id,s_name,s_sex,s_birth
课程表course:c_id,c_name,t_id;
教师表teacher:t_id,,t_name
成绩表score:s_id,c_id,s_score.
1:查询课程编号01成绩大于02课程的学生信息及课程分数。
2:查询每个同学的信息,所学课程总数,总成绩。
3:查询不及格科目在两门及两门以上的同学的学号,姓名,及平均成绩
这是我遇到的一道面试题,后来感觉还有点道道,索性分享一下,献丑了。
如果允许的话,可以自己创建表进行测试,
数据库表结构:
这里就不啰嗦了;直接写答案了:
1:
SELECT stu.*,c1.s_score '01课程' , c2.s_score '02课程' from
(SELECT s_score,s_id from score WHERE c_id=01) c1,
(SELECT s_score,s_id from score WHERE c_id=02) c2,
student stu
WHERE c1.s_id=c2.s_id and stu.s_id=c1.s_id and
c1.s_score>c2.s_score;
2
select s.s_id,s.s_name,t.* from student s,
(select s_id, count(*) '选课总数' ,sum(s_score) '总分' from score group by s_id) t
where s.s_id = t.s_id;
3:
select student.*, avg(s_score) '平均分' from
student,
score
where student.s_id = score.s_id
and
score.s_id in
(select s_id from (select s_id ,count(*) from score where s_score < 60 group by s_id
having count(*) >1) t )
group by student.s_id;
在第三个SQL时,遇到了两个问题:
1:ERROR 1241 (21000): Operand should contain 1 column(s)
出现这个问题主要是因为:in后面只能有一个字段。
2:ERROR 1242 (21000): Subquery returns more than 1 row
这个问题主要是子查询返回了多行数据。