# 第11題：查詢至少有一門課與學(xué)號為“01”的學(xué)生所學(xué)課程相同的學(xué)生的學(xué)號和姓名
SELECT stu.s_id 學(xué)號,s_name 姓名 FROM student stu JOIN (
SELECT DISTINCT s_id
FROM score s RIGHT JOIN (SELECT c_id FROM score WHERE s_id = "01") a ON s.c_id= a.c_id
WHERE s.s_id<>"01")b ON stu.s_id = b.s_id;
/* 以上是自己的解法棚点，此題思路是先找出學(xué)號為01的學(xué)生所學(xué)的課程尊搬，然后把結(jié)果當(dāng)成主表與score表
以c_id相等為條件進行外連接，這樣就把所有有相同課程的人給找出來了途样。
在多表進行聯(lián)合查詢的時候江醇，若查詢的字段在兩個表都有的情況下，這些字段前必須加上表名何暇。
比如此題的stu.s_id 前必須加上stu 而s_name則無需加上stu
*/
#第11題老師的寫法如下：
SELECT a.s_id,a.s_name FROM student AS a
INNER JOIN(
SELECT DISTINCT s_id FROM score
WHERE c_id IN (
SELECT c_id FROM score
WHERE s_id ="01"
)AND s_id !="01"
)AS b ON a.s_id=b.s_id;
# 第12題：查詢與學(xué)號為“01”的學(xué)生所學(xué)課程完全相同的學(xué)生的學(xué)號和姓名
SELECT stu.s_id 學(xué)號,s_name 姓名 FROM student stu JOIN (
SELECT s.s_id FROM score s
LEFT JOIN (SELECT c_id FROM score WHERE s_id = "01") a
ON s.c_id= a.c_id
WHERE s.s_id<>"01" AND a.c_id IS NOT NULL
GROUP BY s.s_id
HAVING COUNT( DISTINCT s.c_id)= (SELECT COUNT(c_id) FROM score WHERE s_id = "01" )
)b ON stu.s_id = b.s_id
/* 以上是自己的解法陶夜，此題思路與第11題大致類似，先找出學(xué)號為01的學(xué)生所學(xué)的課程裆站，然后把此結(jié)果當(dāng)成從表與
主表score以c_id相等為條件進行外連接（那么此時會行成一個表条辟，大致為：凡是與01號學(xué)生所學(xué)課程相同的那一行數(shù)據(jù)，從表的
c_id列都會有該課程的id宏胯，其余為null）,所以只需要把01號學(xué)升自己排除且把從表的c_id字段為null的行排除后羽嫡，按照s_id進行
分組，然后統(tǒng)計課程數(shù)（需要使用distinct胳嘲，用于排重）與01號學(xué)生學(xué)的課程一樣就行了厂僧。
我在做這題的時候，有2個沒考慮周全的問題了牛。
1：count（）是按照*颜屠、s.c_id來的,沒有對s.c_id去重
2：沒有排除a.c_id 為null的數(shù)據(jù)，這樣會帶入臟數(shù)據(jù)鹰祸。比如有個學(xué)生學(xué)的課程id是7 8 9甫窟，也就意味著該學(xué)生有3行a.c_id列為null的
數(shù)據(jù)，統(tǒng)計時會把該學(xué)生統(tǒng)計進去蛙婴。
總結(jié)：這題我感覺還是蠻麻煩的粗井，很多細節(jié)點。
*/
# 第12題：老師的寫法（他貌似第一次也寫錯了街图，考慮的不周全）
SELECT * FROM student
WHERE s_id IN (
SELECT s_id FROM score
WHERE s_id!="01"
GROUP BY s_id HAVING COUNT(DISTINCT c_id)=(SELECT COUNT(DISTINCT c_id) FROM score WHERE s_id="01")
)
AND s_id NOT IN (
SELECT DISTINCT s_id FROM score
WHERE c_id NOT IN(
SELECT c_id FROM score
WHERE s_id="01")
)
# 第13浇衬、14題這邊沒看到題目，所以就沒貼了餐济。為了保證與其同步后面好復(fù)習(xí)耘擂，我也就直接跳到15題了
# 第15題：查詢2門及以上不及格（score<60）的同學(xué)的學(xué)號、姓名及其平均成績
SELECT s.s_id 學(xué)號, stu.s_name 姓名, AVG(s_score)平均成績
FROM score s JOIN student stu
ON s.s_id = stu.s_id
WHERE s_score< 60
GROUP BY s.s_id
HAVING COUNT(DISTINCT s.c_id)>=2;
/* 這題比較簡單絮姆，就是先聯(lián)合查詢醉冤，然后篩選出哪些課的成績是小于60的秩霍，然后再按照id進行分組，
再利用count統(tǒng)計結(jié)果蚁阳，count內(nèi)用distinct是為了排重铃绒，有可能某個人的一門課有2次成績，當(dāng)然這個
要看設(shè)計表時如何設(shè)計的螺捐，我就根據(jù)自己的習(xí)慣加上了颠悬。
*/
# 第15題老師的做法
SELECT a.s_id,a.s_name,AVG(s_score) FROM student AS a
INNER JOIN score AS b
ON a.s_id = b.s_id
WHERE a.s_id IN (
SELECT s_id FROM score
WHERE s_score<60
GROUP BY s_id HAVING COUNT(DISTINCT c_id)>=2
)
GROUP BY s_id,s_name;
# 第16題： 檢索“01”課程分數(shù)小于60，按分數(shù)降序排列的學(xué)生信息
SELECT s.s_id 學(xué)號,s_name 性名,s_sex 性別,s_birth 出生日期,c_id 課程,s_score 分數(shù)
FROM score s JOIN student stu
ON s.s_id = stu.s_id
WHERE c_id = "01" AND s_score<60
ORDER BY s_score DESC;
# 第17題 ：按平均成績從高到低顯示所有學(xué)生的所有課程的成績以及平均成績
SELECT s.s_id 學(xué)號,s.c_id 課程,s.s_score 成績, a.平均成績 平均成績
FROM score s LEFT JOIN (SELECT s_id,AVG(s_score) 平均成績 FROM score GROUP BY s_id) a
ON s.s_id = a.s_id
ORDER BY 平均成績 DESC,成績 DESC;
我的寫法看起來不舒服归粉，不符合日常需求椿疗。如下：
# 第17題老師的寫法，這種方法我覺得還是很6的
SELECT s_id 學(xué)號
,MAX(CASE WHEN c_id="01" THEN s_score ELSE NULL END) 語文
,MAX(CASE WHEN c_id="02" THEN s_score ELSE NULL END) 數(shù)學(xué)
,MAX(CASE WHEN c_id="03" THEN s_score ELSE NULL END) 英語
,AVG(s_score) 平均成績
FROM score
GROUP BY s_id
ORDER BY 平均成績 DESC;
執(zhí)行結(jié)果如下：
/*
老師這種方法我覺得還是很6的糠悼，
巧用了case when then 與 max（）
該題注意的點是届榄，使用 group by后select后必須是被分組的字段或者是聚合函數(shù)，
具體講解見：https://blog.csdn.net/qq403580298/article/details/90756352
因按照s_id分組后倔喂，每個c_id其實只有一個值铝条，所以使用max/min/avg等聚合函數(shù)都OK
最后取得數(shù)都是一樣的
對了 在使用case when 的時候 經(jīng)常漏掉后面的end
*/
# 第18題： 查詢各科成績最高分、最低分席噩、平均分班缰、及格率，中等率悼枢，優(yōu)良率埠忘，優(yōu)秀率：及格>=60,中等為[70,80)，優(yōu)良為：[80,90),優(yōu)秀為>=90
SELECT s.c_id 課程id
,c_name 課程名
,MAX(s_score) 最高分
,MIN(s_score) 最低分
,AVG(s_score) 平均分
,SUM(CASE WHEN s_score>=60 THEN 1 ELSE 0 END)/COUNT(s_score) 及格率
,SUM(CASE WHEN s_score>=70 AND s_score<80 THEN 1 ELSE 0 END)/COUNT(s_score) 中等率
,SUM(CASE WHEN s_score>=80 AND s_score<90 THEN 1 ELSE 0 END)/COUNT(s_score) 優(yōu)良率
,SUM(CASE WHEN s_score>=90 THEN 1 ELSE 0 END)/COUNT(s_score) 優(yōu)秀率
FROM score s JOIN course c
ON s.c_id = c.c_id
GROUP BY s.c_id;
執(zhí)行結(jié)果如下：
/*
此題主要難點是在求各個課程的及格率馒索、中等率..上
思路與17題類似 巧用了 case when then else與 sum（）莹妒。
即設(shè)定一個條件，當(dāng)分數(shù)滿足這個條件時+1绰上，不滿足時+0旨怠。
另外補充一個把小數(shù)轉(zhuǎn)換成百分數(shù)的方法：
select concat(0.6667*100,'%') 得出的結(jié)果為：66.6700%
此時會覺得保留小數(shù)后兩位比較好，那么可以這樣寫：select concat(round(0.6667*100,2),'%')
所以針對上面的SQL可以寫成如下（看起來比較繞）：
*/
SELECT s.c_id 課程id
,c_name 課程名
,MAX(s_score) 最高分
,MIN(s_score) 最低分
,ROUND(AVG(s_score),2) 平均分
,CONCAT(ROUND(SUM(CASE WHEN s_score>=60 THEN 1 ELSE 0 END)/COUNT(s_score)*100,2),'%')及格率
,CONCAT(ROUND(SUM(CASE WHEN s_score>=70 AND s_score<80 THEN 1 ELSE 0 END)/COUNT(s_score)*100,2),'%') 中等率
,CONCAT(ROUND(SUM(CASE WHEN s_score>=80 AND s_score<90 THEN 1 ELSE 0 END)/COUNT(s_score)*100,2),'%') 優(yōu)良率
,CONCAT(ROUND(SUM(CASE WHEN s_score>=90 THEN 1 ELSE 0 END)/COUNT(s_score)*100,2),'%') 優(yōu)秀率
FROM score s JOIN course c
ON s.c_id = c.c_id
GROUP BY s.c_id;
改進后執(zhí)行如下：
# 第19題 ：按各科成績進行排序蜈块，并顯示排名
#rank()over()的使用
SELECT s.s_id 學(xué)號,s_name 姓名,c_name 課程,s_score 成績
,rank()over(PARTITION BY s.c_id ORDER BY s_score DESC) 科內(nèi)排名
FROM student stu
JOIN score s ON stu.s_id = s.s_id
JOIN course c ON s.c_id = c.c_id;
#執(zhí)行結(jié)果如下
#rank()over()的使用,不使用partition by的效果
SELECT s.s_id 學(xué)號,s_name 姓名,c_name 課程,s_score 成績
,rank()over(ORDER BY s_score DESC) 科內(nèi)排名
FROM student stu
JOIN score s ON stu.s_id = s.s_id
JOIN course c ON s.c_id = c.c_id;
#執(zhí)行結(jié)果如下
#row_number()over()的使用
SELECT s.s_id 學(xué)號,s_name 姓名,c_name 課程,s_score 成績
,row_number()over(PARTITION BY s.c_id ORDER BY s_score DESC) 科內(nèi)排名
FROM student stu
JOIN score s ON stu.s_id = s.s_id
JOIN course c ON s.c_id = c.c_id;
#執(zhí)行結(jié)果如下
#dense_rank()over()的使用
SELECT s.s_id 學(xué)號,s_name 姓名,c_name 課程,s_score 成績
,dense_rank()over(PARTITION BY s.c_id ORDER BY s_score DESC) 科內(nèi)排名
FROM student stu
JOIN score s ON stu.s_id = s.s_id
JOIN course c ON s.c_id = c.c_id;
#執(zhí)行結(jié)果如下
/*
總結(jié)：
row_number()over(partition by字段1 order by 字段2) 的結(jié)果是每一行記錄生成一個序號鉴腻，依次排序且排序的序號不會重復(fù)
rank()over(partition by字段1 order by 字段2) 的結(jié)果會考慮排序字段值相同的情況，若排序字段的值相同則其序號是一樣的百揭，
后續(xù)不同字段值的序號為（前一行序號+N,其中N為前一個字段值重復(fù)的行數(shù)）爽哎，比如 1 1 3 4 4 4 7。
dense_rank()over(partition by字段1 order by 字段2) 的結(jié)果也會考慮排序字段值相同的情況器一，即排序字段的值相同那么他們的序號是一樣的课锌，但是與rank（）的區(qū)別是后續(xù)不同字段值的序號為（前一行序號+1），比如 1 1 2 2 3 4 5
另外：over（partition by字段1 order by 字段2）中的partition by 字段1 是可以省略的但是order by 字段2 不可省略
*/
#第20題：對學(xué)生總成績從大到小排序
SELECT stu.s_id 學(xué)號, stu.s_name 姓名
,SUM(s.s_score) 總成績,rank () over (ORDER BY SUM(s.s_score) DESC) 排名
FROM student stu
JOIN score s ON stu.s_id = s.s_id
GROUP BY stu.s_id;
#執(zhí)行結(jié)果如下
