SQL统计实例——mysql实现

网上的经典例题，这里用mysql方言实现。

 

表格如下：

 

mysql建表语句： 

/*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.    计算每个人的总成绩并排名(要求显示字段：姓名，总成绩)

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

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

4.    计算每个人的平均成绩（要求显示字段: 学号，姓名，平均成绩）

5.    列出各门课程成绩最好的学生(要求显示字段: 学号，姓名,科目，成绩)

6.    列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)

7.    统计如下：

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

 

 

 

 

8．列出各门课程的平均成绩（要求显示字段：课程，平均成绩）

9．列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）

10．列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）

11．求出李四的数学成绩的排名

12．统计如下：

课程	不及格（0-59）个	良（60-80）个	优（81-100）个

  

 

 

 

 

 答案：

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

SELECT a.name, SUM(score) sum_score FROM stuscore a GROUP BY a.name

  

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

SELECT a.stuid, a.name, SUM(score) sum_score FROM stuscore a GROUP BY a.name

  

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

SELECT DISTINCT a.stuid, a.name, a.subject, a.score FROM stuscore a
WHERE (
    SELECT COUNT(1) FROM stuscore b
    WHERE b.subject=a.subject AND b.score>=a.score
) <= 2
ORDER BY a.subject ASC, a.score DESC

  

7.    统计如下：

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

 

 

 

 

SELECT a.stuid 学号, a.name 姓名,
    SUM(CASE WHEN a.subject='语文' THEN score ELSE 0 END)语文,
    SUM(CASE WHEN a.subject='数学' THEN score ELSE 0 END)数学,
    SUM(CASE WHEN a.subject='英语' THEN score ELSE 0 END)英语,
    SUM(score)总分, (SUM(score)/COUNT(1))平均分
FROM stuscore a GROUP BY a.stuid

  

8．列出各门课程的平均成绩（要求显示字段：课程，平均成绩）

SELECT a.subject, AVG(score) avg_score FROM stuscore a GROUP BY a.subject

  

9．列出数学成绩的排名（要求显示字段：学号，姓名，成绩，排名）

SELECT a.*, @var:=@var+1 rank
FROM (
    SELECT stuid, name, score FROM stuscore
    WHERE subject='数学' ORDER BY score DESC
)a, (SELECT @var:=0)b

  

10．列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）

SELECT t.* FROM (
    SELECT a.*, @var:=@var+1 rank
    FROM (
        SELECT stuid, NAME, score FROM stuscore
        WHERE SUBJECT='数学' ORDER BY score DESC
    )a, (SELECT @var:=0)b
)t WHERE t.rank IN (2,3)

  

11．求出李四的数学成绩的排名

SELECT t.* FROM (
    SELECT a.*, @var:=@var+1 rank
    FROM (
        SELECT stuid, name, score FROM stuscore
        WHERE subject='数学' ORDER BY score DESC
    )a, (SELECT @var:=0)b
)t WHERE t.name='李四'

  

12．统计如下：

课程	不及格（0-59）个	良（60-80）个	优（81-100）个

  

 

 

 

SELECT a.subject 课程,
    (SELECT COUNT(1) FROM stuscore WHERE subject=a.subject AND score<60)不及格,
    (SELECT COUNT(1) FROM stuscore WHERE subject=a.subject AND score BETWEEN 60 AND 80)良,
    (SELECT COUNT(1) FROM stuscore WHERE subject=a.subject AND score>80)优
FROM stuscore a GROUP BY a.subject