SQL语句使用06--------案例02

继续案例
(表的基本结构：
Student(Sid,Sname,Sage,Ssex) 学生表
Course(Cid,Cname,Tid) 课程表
SC(Sid,Cid,score) 成绩表
Teacher(Tid,Tname) 教师表
)

1.查询没有学全所有课的同学的学号、姓名;

思路：我们要查询没有学全所有课的学生需要以下几步
1.查询出学全所有课有多少
2.查询学生学的课数
3.小于所有课的是没有学全的

select count(Cid) from Course；
select Student.Sid,Student.Sname from Student,SC where Student.Sid=SC.Sid group by  Student.Sid having count(Cid) <(select count(Cid) from Course);

2.按平均成绩从高到低显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数学,语文,英语,课程数,平均分

流程：
1.先利用列子查询获得语文，数学，英语的成绩
2.查询出所有同学的平均成绩
3.查询出所有同学的有效课程，即上过的课程
4.按照SID分类
5.进行平均成绩排序

而其中又有这些问题：

//查出了每个学生的001成绩
SELECT sid,score FROM SC WHERE Cid='001';
//但是虽然这里是顺着排的，但是没有与主表连接起来，没法确定sid是否对应

//所以我为其加入了这样一个步骤
SELECT Sid as 学生ID ,(SELECT score FROM SC WHERE SC.Sid=t.Sid AND Cid='001') AS 数学 FROM SC AS t;
//这样就可以看出学号是对应上了，但是为什么很多重复的呢，因为这里一个学生是有多门课的，但是我们只查出了数学，所以默认赋给了所有的课
//所以可以GROUP BY Sid ，按学号分类，结果就不发出来了

//现在我只需要所有课和平均成绩即可了
//有效课程好办
count(*)
//这里是对select sid from sc;进行累加，因为这个就是查出所有的课。
//平均成绩用
group by sid+ order by avg(t.score)

//最终
SELECT Sid as 学生ID 
        ,(SELECT score FROM SC WHERE SC.Sid=t.Sid AND Cid='001') AS 数学 
        ,(SELECT score FROM SC WHERE SC.Sid=t.Sid AND Cid='002') AS 语文
        ,(SELECT score FROM SC WHERE SC.Sid=t.Sid AND Cid='003') AS 英语 
        ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩 
    FROM SC AS t 
    GROUP BY Sid 
    ORDER BY avg(t.score) ;

3.总结

争取每天分析两题