mysql完成SQL統(tǒng)計(jì)的案例

/*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,'張三','數(shù)學(xué)','89','1'),
(2,'張三','語文','80','1'),
(3,'張三','英語','70','1'),
(4,'李四','數(shù)學(xué)','90','2'),
(5,'李四','語文','70','2'),
(6,'李四','英語','80','2'),
(7,'王五','數(shù)學(xué)','55','3'),
(8,'王五','語文','92','3'),
(9,'王五','英語','74','3'),
(10,'趙六','數(shù)學(xué)','62','4'),
(11,'趙六','語文','81','4'),
(12,'趙六','英語','93','4');

建表語句

問題：

1. 計(jì)算每個(gè)人的總成績并排名(要求顯示字段：姓名，總成績)

答案

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

2. 計(jì)算每個(gè)人的總成績并排名(要求顯示字段: 學(xué)號，姓名，總成績)

答案

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

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

答案

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

答案

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

答案

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

答案

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. 統(tǒng)計(jì)如下：

答案

1 SELECT stuid 學(xué)號, NAME 姓名,
2     SUM(CASE WHEN SUBJECT='語文' THEN score ELSE 0 END) 語文,
3     SUM(CASE WHEN SUBJECT='數(shù)學(xué)' THEN score ELSE 0 END) 數(shù)學(xué),
4     SUM(CASE WHEN SUBJECT='英語' THEN score ELSE 0 END) 英語,
5     SUM(score) 總分, (SUM(score)/COUNT(1)) 平均分
6 FROM stuscore GROUP BY 學(xué)號

8．列出各門課程的平均成績（要求顯示字段：課程，平均成績）

答案

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

9．列出數(shù)學(xué)成績的排名（要求顯示字段：學(xué)號，姓名，成績，排名）

答案

1 SELECT a.*, @var:=@var+1 rank 
2 FROM(
3     SELECT stuid, NAME, score FROM stuscore 
4     WHERE SUBJECT='數(shù)學(xué)' ORDER BY score DESC
5 )a, (SELECT @var:=0)b

10．列出數(shù)學(xué)成績在2-3名的學(xué)生（要求顯示字段：學(xué)號，姓名,科目，成績）

答案

1 SELECT b.* FROM(
2     SELECT a.* FROM(
3         SELECT stuid, NAME, score FROM stuscore 
4         WHERE SUBJECT='數(shù)學(xué)' ORDER BY score DESC 
5         LIMIT 3
6     )a ORDER BY score ASC LIMIT 2
7 )b ORDER BY score DESC
8 
9 #注：當(dāng)數(shù)學(xué)成績只有2條以下數(shù)據(jù)時(shí)，此方法失效！

11．求出李四的數(shù)學(xué)成績的排名

答案

1 SELECT a.*, @var:=@var+1 rank 
2 FROM(
3     SELECT stuid, NAME, score FROM stuscore 
4     WHERE SUBJECT='數(shù)學(xué)' ORDER BY score DESC5 )a, (SELECT @var:=0)b
6 WHERE a.name='李四'

12．統(tǒng)計(jì)如下：

答案

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)優(yōu)
5 FROM stuscore a GROUP BY SUBJECT

以上就是mysql實(shí)現(xiàn)SQL統(tǒng)計(jì)的實(shí)例的詳細(xì)內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！