Home  >  Article  >  Database  >  Example of mysql implementing SQL statistics

Example of mysql implementing SQL statistics

黄舟
黄舟Original
2017-09-08 13:29:401790browse

Create table statement

/*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');

建表语句

Question:

1. Calculate everyone’s total score and rank (required display fields: name, total score)

Answer


##

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

2. Calculate everyone’s total score and rank Ranking (fields required to be displayed: student number, name, total score)

Answer


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

3. Calculate each person’s highest score in a single subject (required to be displayed) Fields: student number, name, course, highest grade)


Answer


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. Calculate the average grade of each person (required to display fields : Student number, name, average grade)


Answer


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. List the students with the best grades in each course (required to display Fields: student number, name, subject, score)


Answer


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. List the two best performers in each course Students (required display fields: student number, name, subject, grade)

Answer

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. The statistics are as follows:


##Total scoreAverage score
Student Number

Name

Chinese

Mathematics

English

Answer

1 SELECT stuid 学号, NAME 姓名,
2     SUM(CASE WHEN SUBJECT=&#39;语文&#39; THEN score ELSE 0 END) 语文,
3     SUM(CASE WHEN SUBJECT=&#39;数学&#39; THEN score ELSE 0 END) 数学,
4     SUM(CASE WHEN SUBJECT=&#39;英语&#39; THEN score ELSE 0 END) 英语,
5     SUM(score) 总分, (SUM(score)/COUNT(1)) 平均分
6 FROM stuscore GROUP BY 学号

8. List the average grades of each course (required display fields: course, average grade)


Answer

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

9. List the rankings of mathematics scores (required display fields: student number, name, grades, ranking)

Answer

1 SELECT a.*, @var:=@var+1 rank 
2 FROM(
3     SELECT stuid, NAME, score FROM stuscore 
4     WHERE SUBJECT=&#39;数学&#39; ORDER BY score DESC
5 )a, (SELECT @var:=0)b

10. List the students who ranked 2-3 in mathematics (required display fields: student number, name, subject, grade)


Answer

1 SELECT b.* FROM(
2     SELECT a.* FROM(
3         SELECT stuid, NAME, score FROM stuscore 
4         WHERE SUBJECT=&#39;数学&#39; 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. Find the ranking of John Doe’s math scores


Answer

1 SELECT a.*, @var:=@var+1 rank 
2 FROM(
3     SELECT stuid, NAME, score FROM stuscore 
4     WHERE SUBJECT=&#39;数学&#39; ORDER BY score DESC5 )a, (SELECT @var:=0)b
6 WHERE a.name=&#39;李四&#39;

12. The statistics are as follows:


##Course

Failed (0-59)

Good (60-80)

Excellent (81-100)

##Answer

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

The above is the detailed content of Example of mysql implementing SQL statistics. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn