mysql實作SQL統計的實例

建表語句

/*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.計算每個人的總成績並排名(要求顯示欄位：姓名，總成績)

答案

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

2.    計算每個人的總成績並排名(要求顯示字段: 學號，姓名，總成績)

答案

#

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

3.    計算每個人單科的最高成績(要求顯示字段: 學號，姓名，課程，最高成績)

答案

#

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.    計算每個人的平均成績（要求顯示字段: 學號，姓名，平均成績）

答案

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.    列出各門課程成績最好的學生(要求顯示欄位: 學號，姓名,科目，成績)

答案

#

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.    列出各門課程成績最好的兩位學生(要求顯示字段: 學號，姓名,科目，成績)

答案

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.    統計如下：

#學號	姓名	#文字	數學	#英文	總分	#平均分數
		#

 

 

 

# # 答案答案

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 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．列出各門課程的平均成績（要求顯示欄位：課程，平均成績）	答案 1 SELECT SUBJECT, AVG(score) avg_score FROM stuscore GROUP BY SUBJECT 9．列出數學成績的排名（要求顯示欄位：學號，姓名，成績，排名）
10．列出數學成績在2-3名的學生（要求顯示欄位：學號，姓名,科目，成績）	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．求李四的數學成績的排名	答案 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='李四' 12．統計如下：

不及格（0-59）個

良（60-80）個

#########優（81-100）個############################################################################# # ############ ############ ############ ####################################################################################################################### #####  ###### ###### #######答案###

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

以上是mysql實作SQL統計的實例的詳細內容。更多資訊請關注PHP中文網其他相關文章！