Beispiel für die Implementierung von SQL-Statistiken durch MySQL-MySQL-Tutorial-php.cn

Beispiel für die Implementierung von SQL-Statistiken durch MySQL

黄舟

Freigeben： 2017-09-08 13:29:40

Original

1800 Leute haben es durchsucht

Anweisung zur Tabellenerstellung

/*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,&#39;张三&#39;,&#39;数学&#39;,&#39;89&#39;,&#39;1&#39;),
(2,&#39;张三&#39;,&#39;语文&#39;,&#39;80&#39;,&#39;1&#39;),
(3,&#39;张三&#39;,&#39;英语&#39;,&#39;70&#39;,&#39;1&#39;),
(4,&#39;李四&#39;,&#39;数学&#39;,&#39;90&#39;,&#39;2&#39;),
(5,&#39;李四&#39;,&#39;语文&#39;,&#39;70&#39;,&#39;2&#39;),
(6,&#39;李四&#39;,&#39;英语&#39;,&#39;80&#39;,&#39;2&#39;),
(7,&#39;王五&#39;,&#39;数学&#39;,&#39;55&#39;,&#39;3&#39;),
(8,&#39;王五&#39;,&#39;语文&#39;,&#39;92&#39;,&#39;3&#39;),
(9,&#39;王五&#39;,&#39;英语&#39;,&#39;74&#39;,&#39;3&#39;),
(10,&#39;赵六&#39;,&#39;数学&#39;,&#39;62&#39;,&#39;4&#39;),
(11,&#39;赵六&#39;,&#39;语文&#39;,&#39;81&#39;,&#39;4&#39;),
(12,&#39;赵六&#39;,&#39;英语&#39;,&#39;93&#39;,&#39;4&#39;);

建表语句

Nach dem Login kopieren

Frage:

1. Berechnen Sie die Gesamtpunktzahl und den Rang jeder Person (erforderliche Anzeigefelder: Name, Gesamtpunktzahl)

Antwort

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

Nach dem Login kopieren

2. Berechnen Sie die Gesamtpunktzahl und Rangfolge (erforderliche Anzeigefelder: Studierendenausweis, Name, Gesamtpunktzahl)

Antwort

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

Nach dem Login kopieren

3. Berechnen Sie das Einzelfach der Person höchste Punktzahl (erforderliche Anzeigefelder: Studierendenausweis, Name, Kurs, höchste Punktzahl)

Antwort

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

Nach dem Login kopieren

4 (erforderliche Anzeigefelder: Schülernummer, Name, Durchschnittsnote)

Antworten

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

Nach dem Login kopieren

5. Listen Sie jeweils den besten Schüler auf Punktzahl in jedem Kurs (erforderliche Anzeigefelder: Studentennummer, Name, Fach, Note)

Antwort

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

Nach dem Login kopieren

6 zwei Studierende mit den besten Ergebnissen in jedem Kurs (erforderliche Anzeigefelder: Studierendennummer, Name, Fach, Note)

Antwort

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

Nach dem Login kopieren

7. Die Statistik lautet wie folgt:

< td valign="top" width="53">

Gesamtpunktzahl

< / tr>

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

Studentenausweis

< / td>

Name

Sprache

Mathe

Englisch

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 学号

Nach dem Login kopieren

Durchschnittspunktzahl

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

Nach dem Login kopieren

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

Nach dem Login kopieren

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条以下数据时，此方法失效！

Nach dem Login kopieren

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;

Nach dem Login kopieren

Antwort

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

8． Listen Sie die Durchschnittsnoten jedes Kurses auf (erforderliche Anzeigefelder: Kurs, Durchschnittsnote)

Antwort

9. Listen Sie die Rangfolge der Mathematikergebnisse auf (erforderliche Anzeigefelder: Schülernummer, Name, Noten, Rangfolge)

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

Nach dem Login kopieren

Antwort10. Listen Sie die Schüler mit Mathe-Ergebnissen auf Platz 2-3 auf (erforderliche Anzeigefelder: Schülernummer, Name, Fach, Note) Antworten11. Finden Sie die Rangfolge der Mathematikergebnisse von John DoeAntwort12. Die Statistiken lauten wie folgt: tr >

Kurs	Misserfolge (0-59)	Gute (60-80) Stücke	Ausgezeichnete (81-100) Stücke
	td >

Antwort

Das obige ist der detaillierte Inhalt vonBeispiel für die Implementierung von SQL-Statistiken durch MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!