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

不念不忘少年蓝@ 2022-06-08 03:55 237阅读 0赞

继续案例  
(表的基本结构：  
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);

![这里写图片描述][SouthEast]

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

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

而其中又有这些问题：

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

![这里写图片描述][SouthEast 1]

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

![这里写图片描述][SouthEast 2]

//现在我只需要所有课和平均成绩即可了
    //有效课程好办
    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) ;

![这里写图片描述][SouthEast 3]

# 3.总结 #

争取每天分析两题

[SouthEast]: /images/20220608/c35028f9049d4a8e96fbfc8316eca457.png
[SouthEast 1]: /images/20220608/5057278eb9ae41a5a2469a728c450acc.png
[SouthEast 2]: /images/20220608/07b887db8be44d1f919c54a9d59bcccd.png
[SouthEast 3]: /images/20220608/babc5de8657b4a6cb947b568a11e213b.png