这几天在练习sql的时候,碰到下面几个题, 如下
他的表字段是这些
create table Student(
SId varchar(10), # 学生id
Sname varchar(10), # 学生姓名
Sage datetime, # 学生出生日期
Ssex varchar(10) # 学生性别
);
create table Course(
CId varchar(10), # 课程id
Cname nvarchar(10), # 课程名
TId varchar(10) # 任课教师id
);
create table Teacher(
TId varchar(10), # 任课教师id
Tname varchar(10) # 教师姓名
);
create table SC(
SId varchar(10), # 学生id
CId varchar(10), # 课程id
score decimal(18,1) # 分数
);
之前呢总想着 用子查询 把所满足条件的sid查出来,在通过 in 判断sid是否在满足条件的sid里
思想没有转变,现在有了这样一种思想,用 not in ,来解决不太好用子查询来查询满足条件的查询。 就是通过判断sid他不在那些不满足条件的sid中
-- 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select s.sid,s.sname,avg(sc.score) from student s join sc on s.sid not in (select distinct sid from sc where sid not in (select sid from sc where score<60 group by sid having count(cid)>=2)) and s.sid=sc.sid group by sid select s.sid,s.sname,avg(sc.score) from Student s join SC on s.sid=sc.sid join (select sid,count(score) c from sc where score<60 group by sid having c>=2) a on s.sid = a.sid group by s.sid
-- 查询各科成绩最高分、最低分和平均分: 以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
select * from (select sc.cid as x,c.cname,max(sc.score) zg,min(sc.score) zd,avg(sc.score) pj from sc join Course c on sc.cid=c.cid group by sc.cid) aa join (select cid as x,concat(count(CASE WHEN 80>score and score>=70 then '中等' end)/count(cid)*100,'%') zd, concat(count(CASE WHEN 90>score and score>=80 then '优良' end)/count(cid)*100,'%') yl, concat(count(CASE WHEN 100>score and score>=90 then '优秀' end)/count(cid)*100,'%') yx, concat(count(case when score>=60 then '及格' end)/count(cid)*100,'%') jg from sc group by sc.cid) bb on aa.x=bb.x //查询成绩的区间 (select cid,concat(count(CASE WHEN 80>score and score>=70 then '中等' end)/count(cid)*100,'%') zd, concat(count(CASE WHEN 90>score and score>=80 then '优良' end)/count(cid)*100,'%') yl, concat(count(CASE WHEN 100>score and score>=90 then '优秀' end)/count(cid)*100,'%') yx, concat(count(case when score>=60 then '及格' end)/count(cid)*100,'%') jg from sc group by cid)