Maison > Article > base de données > Exemple de MySQL implémentant des statistiques SQL
Déclaration de création de table
/*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. Calculez le score total et le classement de chaque personne (champs d'affichage obligatoires : nom, score total)
Réponse
1 SELECT a.name, SUM(score) sum_score FROM stuscore a 2 GROUP BY a.name ORDER BY sum_score DESC
2. Calculez le score total de chaque personne. score total et classement (champs d'affichage obligatoires : carte d'étudiant, nom, score total)
Réponse
1 SELECT a.stuid, a.name, SUM(score) sum_score FROM stuscore a 2 GROUP BY a.name ORDER BY sum_score DESC
Calculer le sujet unique de chaque personne. score le plus élevé (champs d'affichage obligatoires : numéro d'étudiant, nom, cours, score le plus élevé)
Réponse
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. (Champs d'affichage obligatoires : numéro d'élève, nom, note moyenne)
Réponse
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. score dans chaque cours (champs d'affichage obligatoires : numéro d'étudiant, nom, matière, note)
Réponse
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 . deux étudiants ayant les meilleurs scores dans chaque cours (champs d'affichage obligatoires : numéro d'étudiant, nom, matière, note)
Réponse
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
Les statistiques sont les suivantes :
|
Nom | Langue | Mathématiques | Anglais1 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 学号 |
Score moyen | ||||||||||||||
1 SELECT SUBJECT, AVG(score) avg_score FROM stuscore GROUP BY SUBJECT |
|
|
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 |
|
|
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条以下数据时,此方法失效! |
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='李四'
Réponse
课程 |
不及格(0-59)个 |
良(60-80)个 |
优(81-100)个 |
|
|
|
|
8. Lister les notes moyennes de chaque cours (champs d'affichage obligatoires : cours, note moyenne)
Réponse9. Lister le classement des scores en mathématiques (champs d'affichage obligatoires : numéro d'élève, nom, notes, classement)
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 SUBJECTRéponse10. Répertoriez les élèves avec des résultats en mathématiques à la 2-3ème place (champs d'affichage obligatoires : numéro d'élève, nom, matière, note) Réponses11. Retrouvez le classement des résultats en mathématiques de John DoeRéponse12. Les statistiques sont les suivantes :
Cours | Échecs (0-59) | Bons (60-80) pièces | Excellents (81-100) pièces |
td > | tr > |
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!