mysql复杂查询—学生-课程-成绩

    学生表：id  name 

    教师表:   id  name

    课程表:   id  name  teacher_id

    成绩表:   student_id   course_id     score

1.查询总成绩小于60的学生id以及姓名                            (根据用户分组 通过having对平均成绩进行筛选)

     select sc.student_id,st.name ,sum(score) total from score sc
     left join student st on st.id=sc.student_id 
     left join course c on c.id=sc.course_id group by sc.student_id 
     having total<60

2.查询李老师的个数

     select count(*) from teacher where name like '李%';

3.查询所有同学的学号、姓名、选课数、总成绩 

     select st.id,st.name ,count(course_id) total_course,sum(score) total_score from student st
     left join score sc  on st.id=sc.student_id
     left join course c on c.id = sc.course_id
     group by st.id

（有反映这条sql有问题，直接复制确实会执行不了，只要去掉中间的空格就好了，然后又改了一下）

       select st.id,st.name,count(course_id) total_course,sum(ifnull(score,0)) total_score from student st left join score sc on st.id=sc.student_id left join course c on c.id = sc.course_id group by st.id

4.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；

     select sc.course_id,c.name,avg(score) score from score sc 
     left join course c on sc.course_id=c.id group by sc.course_id
     order by score ,c.id desc

5.查询课程名称为“数学”，且分数低于60的学生姓名和分数

     select st.id,st.name from score sc
     left join course c on c.id=sc.course_id
     left join student st on st.id=sc.student_id
     where c.name="数学" and score<60

6.求选了课程的人数

     select count(distinct(student_id)) from score

7. 检索至少选修两门课程的学生学号

    select student_id,count(student_id) course ,score from score group by student_id having course>=2 

8.查询全部学生都选修的课程的课程号和课程名(从学生表中统计出学生总数，在成绩表中根据课程分组，选择每门课程的人数等于学生总数)

     select sc.course_id,c.name,count(student_id) num from score sc
     left join course c on c.id=sc.course_id
     left join student st on st.id=sc.student_id
     group by sc.course_id
     having num=(select count(*) from student)

9. 查询“语文”课程比“数学”课程成绩高的所有学生的学号；

思路： （1）获取所有选了语文课程的学生的成绩（学号，成绩） --临时表

　　    （2）获取所有选了数学课程的学生的成绩（学号，成绩） --临时表

　　　（3）根据学号连接两张临时表（学号，语文成绩，数学成绩），加条件进行查询

     select a.student_id,yuwen 语文,shuxue 数学 from
     (select sc.student_id,sc.score yuwen from score sc
     left join course c on c.id=sc.course_id
     where c.name="语文") as a
     left join(select sc.student_id,sc.score shuxue from score sc
     left join course c on c.id=sc.course_id
     where c.name="数学") as b 
     on a.student_id=b.student_id where yuwen> if(isnull(shuxue),0,shuxue);

 

来源：http://www.cnblogs.com/tomato0906/articles/7414142.html