SQL學生表 — 2 · Python / Linux / Mysql / 軟件測試等等

接著上一篇[https://www.cnblogs.com/yoyoketang/p/10065424.html](https://www.cnblogs.com/yoyoketang/p/10065424.html)，繼續學生表SQL * 1.計算每個人的平均成績，要求顯示字段: 學號，姓名，平均成績 * 2.計算每個人的成績，總分數，平均分，要求顯示：學號，姓名，語文，數學，英語，總分，平均分 * 3.列出各門課程的平均成績，要求顯示字段：課程，平均成績 * 4.列出數學成績的排名，要求顯示字段：學號，姓名，成績，排名 ## **萬年不變學生表** 有2張表，學生表（student）基本信息如下 ![](https://img.kancloud.cn/7b/dd/7bddde959bbe4616f75b522ca88d2ace_519x280.png) 科目和分數表（grade） ![](https://img.kancloud.cn/6e/7d/6e7d27edc175fd7abf3b34189226262b_521x340.png) ### **計算學生平均分數** 1.計算每個人的平均成績，要求顯示字段: 學號，姓名，平均成績 ``` select a.id, a.name, avg(b.score) from student a, grade b where a.id=b.id group by id ``` 或者 ~~~ select a.id, a.name, c.avg_score from student a, (select b.id, avg(b.score) as avg_score from grade b group by b.id )c where a.id = c.id ~~~ ![](https://img.kancloud.cn/41/f9/41f9dc458eb9c0216e04d258aaf3faf9_356x331.png) ### **統計各科目成績** 2.計算每個人的成績，總分數，平均分，要求顯示：學號，姓名，語文，數學，英語，總分，平均分使用case when 語法把科目字段分解成具體的科目：語文，數學，英語 ~~~ select a.id as 學號, a.name as 姓名, (case when b.kemu='語文' then score else 0 end) as 語文, (case when b.kemu='數學' then score else 0 end) as 數學, (case when b.kemu='英語' then score else 0 end) as 英語 from student a, grade b where a.id = b.id ~~~ ![](https://img.kancloud.cn/4b/d2/4bd206c14d2becb342e7876e7dc1a52e_649x443.png) ~~~ SELECT a.id as 學號, a.name as 姓名, sum(case when b.kemu='語文' then score else 0 end) as 語文, sum(case when b.kemu='數學' then score else 0 end) as 數學, sum(case when b.kemu='英語' then score else 0 end) as 英語, sum(b.score) as 總分 , sum(b.score)/count(b.score) as 平均分 FROM student a, grade b where a.id = b.id GROUP BY b.id, b.id ~~~ ![](https://img.kancloud.cn/40/c0/40c04656a6178c20bc7a9519a01914d8_630x317.png) ### **每門課程平均成績** 3.列出各門課程的平均成績，要求顯示字段：課程，平均成績 ~~~ select b.kemu, avg(b.score) from grade b group by b.kemu ~~~ ![](https://img.kancloud.cn/7b/f3/7bf331e99ec97896ff8c1447df81fab7_530x207.png) ### **成績排名** 4.列出數學成績的排名，要求顯示字段：學號，姓名，成績，排名在查詢結果表里面添加一個變量@paiming,讓它自動加1 ~~~ SELECT t.id, t.score as 數學分數, @paiming := @paiming+1 as 排名 FROM (SELECT b.id, b.score FROM grade b WHERE b.kemu = '數學' ORDER BY score DESC) AS t, (SELECT @paiming := 0) r ~~~ ![](https://img.kancloud.cn/56/ee/56ee615757cc4d3f26e6a888eff133b9_647x318.png) 結合student表獲取學生名稱 ~~~ SELECT t.id, a.name,t.score as 數學分數, @paiming := @paiming+1 as 排名 FROM (SELECT b.id, b.score FROM grade b WHERE b.kemu = '數學' ORDER BY score DESC) AS t, (SELECT @paiming := 0) r, student a WHERE a.id = t.id ~~~ ![](https://img.kancloud.cn/b2/7c/b27c7cd1d33065880f6fb663e9a286cc_682x356.png) ### **同結果名次相同** 上圖由于同一個分數的小伙伴，排名不一樣，本著公平、公正、公開的原則，同一分數名次一樣 ~~~ SELECT t.id, a.name,t.score as 數學分數, (CASE WHEN @temp = t.score THEN @paiming WHEN @temp := t.score THEN @paiming :=@paiming + 1 WHEN @temp = 0 THEN @paiming :=@paiming + 1 END) AS num FROM (SELECT b.id, b.score FROM grade b WHERE b.kemu = '數學' ORDER BY score DESC) AS t, (SELECT @paiming := 0, @temp := 0) r, student a WHERE a.id = t.id ~~~ ![](https://img.kancloud.cn/73/5c/735c6dbe46ba5c2ad475f9cbf91d062f_610x498.png) ### 排名相同的占個名次 ~~~ SELECT obj.id, obj.score as 數學, @rownum := @rownum + 1 AS num_tmp, @incrnum := (CASE WHEN @rowtotal = obj.score THEN @incrnum WHEN @rowtotal := obj.score THEN @rownum END) AS 排名 FROM (SELECT id, score FROM grade WHERE kemu = "數學" ORDER BY score DESC ) AS obj, (SELECT @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0) r ~~~ ![](https://img.kancloud.cn/31/51/3151433cd9886543ca522e72de80178a_693x454.png)