mysql查询学生平均成绩及其名次_sql 统计 学生成绩2

文章来源：http://www.ynpxrz.com/n822738c2024.aspx

t_tudent(sid,sname,sage,ssex,sdept) 学生表

t_course(cid,cname,tid) 课程表

t_score( scid,sid,cid,grade) 成绩表

t_teacher(tid,tname) 教师表

问题：

1、查询“001”课程比“002”课程成绩高的所有学生的学号;

select t1.sid from(select sid,grade from t_score where cid = '001') t1,

(select sid,grade from t_score where cid = '002') t2where t1.grade >t2.grade and t1.sid =t2.sid;selectt2.sid,t2.ca,t2.cb(select t1.sid,sum(t1.ca) ca,sum(t1.cb) cbfrom(selectsid,CASE WHEN cid = '001' THEN grade ELSE 0 ENDca,CASE WHEN cid = '002' THEN grade ELSE 0 ENDcbfromt_score

)t1GROUP BYsid

)t2where t2.ca > t2.cb

View Code

2、查询平均成绩大于60分的同学的学号和平均成绩;

select sid,avg(grade ) from t_score

group by sid

having avg(grade ) >60;

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

select t1.sid,t1.sname,count(t2.cid),sum(grade ) from t_student t1

left join t_score t2

on t1.sid = t2sid

group by t1sid,t1.sname;

4、查询姓“李”的老师的个数;

select count(distinct(tname)) from t_teacher where tname like '李%';

5、查询没学过“叶平”老师课的同学的学号、姓名;

select sid, sname from t_student

where sid not in

(

select distinct(t1.sid) from t_score t1,t_course t2,t_teacher t3

where t1.cid = t2.cid and t3.tid = t2.tid and t3.tname = '叶平'

);

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;

select t0.sid,t0.sname from t_student t0, t_score t1

where t0.sid = t1.sid and t1.cid = '001'

and exists

(

select * from t_score t2 where t2.sid = t1.sid and t2.cid = '002'

);

7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;

select sid,sname from t_student

where sid in

(

select sid from t_score t1 ,t_course t2,t_teacher t3

where t1.cid=t2.cid

and t3.tid=t2.tid

and t3.tname = '叶平'

group by sid

having count(t1.cid)=(

select count(cid) from t_course t1,t_teacher t2

where t2.tid=t1.tid and t2.tname = '叶平'

)

);

8、查询“001”课程比“002”课程成绩高的所有同学的学号、姓名;

Select sid,sname from

(

select t1.sid,t1.sname,t2.grade ,

(

select grade from t_score t3

where t3.sid = t1.sid and t3.cid = '002'

) grade2

from t_student t1, t_score t2

where t1sid =