mysql 的奇妙歷險
mysql 的奇妙歷險
這幾天在練習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) # 分數
);
not in 的使用
之前呢總想着 用子查詢 把所滿足條件的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
case when 的應用：
case when 條件 then 返回值 end
— 查詢各科成績最高分、最低分和平均分： 以如下形式顯示：課程 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)
