建表語句
/*Table?structure?for?table?`stuscore`?*/ DROP?TABLE?IF?EXISTS?`stuscore`; CREATE?TABLE?`stuscore`?( ??`id`?int(11)?NOT?NULL?AUTO_INCREMENT, ??`name`?varchar(20)?DEFAULT?NULL, ??`subject`?varchar(20)?DEFAULT?NULL, ??`score`?varchar(20)?DEFAULT?NULL, ??`stuid`?varchar(10)?DEFAULT?NULL, ??PRIMARY?KEY?(`id`) )?ENGINE=InnoDB?AUTO_INCREMENT=13?DEFAULT?CHARSET=utf8; /*Data?for?the?table?`stuscore`?*/ insert??into?`stuscore`(`id`,`name`,`subject`,`score`,`stuid`)?values? (1,'張三','數學','89','1'), (2,'張三','語文','80','1'), (3,'張三','英語','70','1'), (4,'李四','數學','90','2'), (5,'李四','語文','70','2'), (6,'李四','英語','80','2'), (7,'王五','數學','55','3'), (8,'王五','語文','92','3'), (9,'王五','英語','74','3'), (10,'趙六','數學','62','4'), (11,'趙六','語文','81','4'), (12,'趙六','英語','93','4'); 建表語句
?
?
問題：
1.??? 計算每個人的總成績并排名(要求顯示字段：姓名，總成績)
答案
1?SELECT?a.name,?SUM(score)?sum_score?FROM?stuscore?a 2?GROUP?BY?a.name?ORDER?BY?sum_score?DESC
2.??? 計算每個人的總成績并排名(要求顯示字段: 學號，姓名，總成績)
答案
1?SELECT?a.stuid,?a.name,?SUM(score)?sum_score?FROM?stuscore?a 2?GROUP?BY?a.name?ORDER?BY?sum_score?DESC
3.??? 計算每個人單科的最高成績(要求顯示字段: 學號，姓名，課程，最高成績)
答案
1?SELECT?a.stuid,?a.name,?a.subject,?a.score?FROM?stuscore?a 2?JOIN?( 3?????SELECT?stuid,?MAX(score)?max_score?FROM?stuscore?GROUP?BY?stuid4?)b?ON?a.stuid=b.stuid5?WHERE?a.score=b.max_score
4.??? 計算每個人的平均成績（要求顯示字段: 學號，姓名，平均成績）
答案
1?SELECT?DISTINCT?a.stuid,?a.name,?b.avg_score?FROM?stuscore?a 2?JOIN?( 3?????SELECT?stuid,?AVG(score)?avg_score?FROM?stuscore?GROUP?BY?stuid 4?)b?ON?a.stuid=b.stuid
5.??? 列出各門課程成績最好的學生(要求顯示字段: 學號，姓名,科目，成績)
答案
1?SELECT?DISTINCT?a.stuid,?a.name,?a.subject,?a.score?FROM?stuscore?a 2?JOIN?( 3?????SELECT?subject,?MAX(score)?max_score?FROM?stuscore?GROUP?BY?subject 4?)b?ON?a.subject=b.subject5?WHERE?a.score=b.max_score
6.??? 列出各門課程成績最好的兩位學生(要求顯示字段: 學號，姓名,科目，成績)
答案
1?SELECT?a.stuid,?a.name,?a.subject,?a.score?FROM?stuscore?a 2?WHERE?( 3?????SELECT?COUNT(1)?FROM?stuscore?b 4?????WHERE?a.subject=b.subject?AND?b.score>=a.score 5?)?<p>7.??? 統計如下：<br></p>
|
學號 |
姓名 |
語文 |
數學 |
英語 |
總分 |
平均分 |
|
? |
? |
? |
? |
? |
? |
? |
?
?
?
答案
1?SELECT?stuid?學號,?NAME?姓名, 2?????SUM(CASE?WHEN?SUBJECT='語文'?THEN?score?ELSE?0?END)?語文, 3?????SUM(CASE?WHEN?SUBJECT='數學'?THEN?score?ELSE?0?END)?數學, 4?????SUM(CASE?WHEN?SUBJECT='英語'?THEN?score?ELSE?0?END)?英語, 5?????SUM(score)?總分,?(SUM(score)/COUNT(1))?平均分 6?FROM?stuscore?GROUP?BY?學號
8．列出各門課程的平均成績（要求顯示字段：課程，平均成績）
答案
1?SELECT?SUBJECT,?AVG(score)?avg_score?FROM?stuscore?GROUP?BY?SUBJECT
9．列出數學成績的排名（要求顯示字段：學號，姓名，成績，排名）
答案
1?SELECT?a.*,?@var:=@var+1?rank? 2?FROM( 3?????SELECT?stuid,?NAME,?score?FROM?stuscore? 4?????WHERE?SUBJECT='數學'?ORDER?BY?score?DESC 5?)a,?(SELECT?@var:=0)b
10．列出數學成績在2-3名的學生（要求顯示字段：學號，姓名,科目，成績）
答案
1?SELECT?b.*?FROM( 2?????SELECT?a.*?FROM( 3?????????SELECT?stuid,?NAME,?score?FROM?stuscore? 4?????????WHERE?SUBJECT='數學'?ORDER?BY?score?DESC? 5?????????LIMIT?3 6?????)a?ORDER?BY?score?ASC?LIMIT?2 7?)b?ORDER?BY?score?DESC 8? 9?#注：當數學成績只有2條以下數據時，此方法失效！
11．求出李四的數學成績的排名
答案
1?SELECT?a.*,?@var:=@var+1?rank? 2?FROM( 3?????SELECT?stuid,?NAME,?score?FROM?stuscore? 4?????WHERE?SUBJECT='數學'?ORDER?BY?score?DESC5?)a,?(SELECT?@var:=0)b 6?WHERE?a.name='李四'
12．統計如下：
|
課程 |
不及格（0-59）個 |
良（60-80）個 |
優（81-100）個 |
|
? |
? |
? |
? |
??
?
?
答案
1?SELECT?a.subject?課程, 2?????(SELECT?COUNT(1)?FROM?stuscore?WHERE?score80?AND?SUBJECT=a.subject)優 5?FROM?stuscore?a?GROUP?BY?SUBJECT
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
