

1 /*Table structure for table `stuscore` */ 2 3 DROP TABLE IF EXISTS `stuscore`; 4 5 CREATE TABLE `stuscore` ( 6 `id` int(11) NOT NULL AUTO_INCREMENT, 7 `name` varchar(20) DEFAULT NULL, 8 `subject` varchar(20) DEFAULT NULL, 9 `score` varchar(20) DEFAULT NULL, 10 `stuid` varchar(10) DEFAULT NULL, 11 PRIMARY KEY (`id`) 12 ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; 13 14 /*Data for the table `stuscore` */ 15 16 insert into `stuscore`(`id`,`name`,`subject`,`score`,`stuid`) values 17 (1,'张三','数学','89','1'), 18 (2,'张三','语文','80','1'), 19 (3,'张三','英语','70','1'), 20 (4,'李四','数学','90','2'), 21 (5,'李四','语文','70','2'), 22 (6,'李四','英语','80','2'), 23 (7,'王五','数学','55','3'), 24 (8,'王五','语文','92','3'), 25 (9,'王五','英语','74','3'), 26 (10,'赵六','数学','62','4'), 27 (11,'赵六','语文','81','4'), 28 (12,'赵六','英语','93','4');建表语句

问题:
1. 计算每个人的总成绩并排名(要求显示字段:姓名,总成绩)


SELECT a.name, SUM(score) sum_score FROM stuscore a GROUP BY a.name ORDER BY sum_score DESC答案
2. 计算每个人的总成绩并排名(要求显示字段: 学号,姓名,总成绩)


SELECT a.stuid, a.name, SUM(score) sum_score FROM stuscore a GROUP BY a.name ORDER BY sum_score DESC答案
3. 计算每个人单科的最高成绩(要求显示字段: 学号,姓名,课程,最高成绩)


SELECT a.stuid, a.name, a.subject, a.score FROM stuscore a
JOIN (
SELECT stuid, MAX(score) max_score FROM stuscore GROUP BY stuid
)b ON a.stuid=b.stuid
WHERE a.score=b.max_score 答案4. 计算每个人的平均成绩(要求显示字段: 学号,姓名,平均成绩)


SELECT DISTINCT a.stuid, a.name, b.avg_score FROM stuscore a
JOIN (
SELECT stuid, AVG(score) avg_score FROM stuscore GROUP BY stuid
)b ON a.stuid=b.stuid 答案5. 列出各门课程成绩最好的学生(要求显示字段: 学号,姓名,科目,成绩)


SELECT DISTINCT a.stuid, a.name, a.subject, a.score FROM stuscore a
JOIN (
SELECT subject, MAX(score) max_score FROM stuscore GROUP BY subject
)b ON a.subject=b.subject
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 ) <= 2 6 ORDER BY a.subject ASC, a.score DESC答案
7. 统计如下:
| 学号 | 姓名 | 语文 | 数学 | 英语 | 总分 | 平均分 |


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.列出各门课程的平均成绩(要求显示字段:课程,平均成绩)


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 DESC 5 )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 score<60 AND SUBJECT=a.subject)不及格, 3 (SELECT COUNT(1) FROM stuscore WHERE score BETWEEN 60 AND 80 AND SUBJECT=a.subject)良, 4 (SELECT COUNT(1) FROM stuscore WHERE score>80 AND SUBJECT=a.subject)优 5 FROM stuscore a GROUP BY SUBJECT答案