SQL统计实例——mysql实现SQL统计实例——mysql实现

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');
建表语句

SQL统计实例——mysql实现

问题:

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

SQL统计实例——mysql实现SQL统计实例——mysql实现

SELECT a.name, SUM(score) sum_score FROM stuscore a
 GROUP BY a.name ORDER BY sum_score DESC
答案

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

SQL统计实例——mysql实现SQL统计实例——mysql实现

SELECT a.stuid, a.name, SUM(score) sum_score FROM stuscore a
 GROUP BY a.name ORDER BY sum_score DESC
答案

3.    计算每个人单科的最高成绩(要求显示字段: 学号,姓名,课程,最高成绩)

SQL统计实例——mysql实现SQL统计实例——mysql实现

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.    计算每个人的平均成绩(要求显示字段: 学号,姓名,平均成绩)

SQL统计实例——mysql实现SQL统计实例——mysql实现

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.    列出各门课程成绩最好的学生(要求显示字段: 学号,姓名,科目,成绩)

SQL统计实例——mysql实现SQL统计实例——mysql实现

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.    列出各门课程成绩最好的两位学生(要求显示字段: 学号,姓名,科目,成绩)

SQL统计实例——mysql实现SQL统计实例——mysql实现

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.    统计如下:

学号 姓名 语文 数学 英语 总分 平均分

SQL统计实例——mysql实现SQL统计实例——mysql实现

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

SQL统计实例——mysql实现SQL统计实例——mysql实现

SELECT SUBJECT, AVG(score) avg_score FROM stuscore GROUP BY SUBJECT
答案

9.列出数学成绩的排名(要求显示字段:学号,姓名,成绩,排名)

SQL统计实例——mysql实现SQL统计实例——mysql实现

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名的学生(要求显示字段:学号,姓名,科目,成绩)

SQL统计实例——mysql实现SQL统计实例——mysql实现

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.求出李四的数学成绩的排名

SQL统计实例——mysql实现SQL统计实例——mysql实现

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)个

SQL统计实例——mysql实现SQL统计实例——mysql实现

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
答案