mysql查询的一个问题

最新推荐文章于 2025-01-10 17:01:35 发布

原创最新推荐文章于 2025-01-10 17:01:35 发布 · 527 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

数据库专栏收录该内容

12 篇文章

订阅专栏

本文详述了一道关于MySQL查询的问题，涉及多表连接、分组查询、组函数及排序。问题要求按老师名字列出每个科目学生的平均分、最低分、最高分和总分，按平均分降序排列。解题过程逐步解析了如何通过JOIN、GROUP BY、HAVING和ORDER BY等SQL语句实现这一目标。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

mysql查询的一个问题

带着问题去学习，然后将这个过程中涉及到的知识点都学习一遍

查询出每个老师(要有老师名字) 对应科目所有学生的平均分，最低分，最高分，和总分,并且按照平均分降序排序

数据表结构介绍

t_student：有字段s_id,s_name,t_id，学生的id，名字，学生所属老师id

t_teacher：有字段t_id,t_name，教师d，名字

t_score：有字段scorre_id，score_score，score_subject，stu_id，成绩id，分数，科目，是哪个学生的成绩。

表的sql放到了，最后

分析一下问题：

看到“每个老师”，“对应科目”，会想到分组查询，需要按照老师来分组，然后再根据科目来分组
分组后，需要用到组函数，AVG,MAX,MIN，得出平均分，最低分，最高分
按照平均分降序，需要用到排序 ORDER BY DESC （DESC代表降序，ASC代表升序，默认是升序）
需要的这几个字段分布在三个表中，要多表连接查询

答案

SELECT r.*
FROM(
SELECT st.t_id,t.t_name,sc.score_subject,AVG(sc.score_score) avg_score,MIN(sc.score_score) 最低分,MAX(sc.score_score) 最高分,SUM(sc.score_score) 总分
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
INNER JOIN t_teacher t
ON st.t_id=t.t_id
GROUP BY st.t_id,sc.score_subject
)r
ORDER BY r.avg_score DESC;

忽然一下上来这么多，有点懵，我们分解开来看，先由一个个的小问题，积累解决

解题

我们先从简单的，一步一步来。

查每个老师有多少的学生

SELECT s.t_id,COUNT(s.t_id) # 用到了计数的组函数count
FROM t_student s
GROUP BY s.t_id;  # 按照 教师 分组

+------+---------------+
| t_id | COUNT(s.t_id) |
+------+---------------+
|    1 |             3 |
|    2 |             2 |
+------+---------------+
2 rows in set (0.00 sec)

我们只需要对学生表按照教师的id分组，来单表查询，使用组函数计数

可是我们发现，这里只有老师的id，显示出来不好看

那么，我们就可以做一个连接查询，将教师表和学生表连接起来（通过教师的id，他们都有的字段）

SELECT s.t_id,t.t_name,COUNT(s.t_id) # 用到了计数的组函数count
FROM t_student s
INNER JOIN t_teacher t # 通过t_id，来内连接学生表和教师表
ON t.t_id=s.t_id
GROUP BY s.t_id;  # 按照 教师 分组

+------+--------+---------------+
| t_id | t_name | COUNT(s.t_id) |
+------+--------+---------------+
|    1 | 张老师 |             3 |
|    2 | 王老师 |             2 |
+------+--------+---------------+
2 rows in set (0.00 sec)

教师姓名在t_teacher中，所以我们需要和t_student来做连接查询，使用的是内连接 INNER JOIN … ON

连接还有左外连接和右外连接(LEFT JOIN 和 RIGHT JOIN)，按照左外连接为例：左表中有的记录，必定出来，如果右表中没有对应的，那么值设置为null（可以想一下，先把select选中的字段头写出来，然后，左表的所有记录都填进去，右表的只填有共同特点的）

可以参考https://blog.youkuaiyun.com/plg17/article/details/78758593，这里图解很清晰

查每个老师学生的平均值

SELECT s.t_id,AVG(sc.score_score)
FROM t_student s
INNER JOIN t_score sc
ON sc.stu_id=s.s_id
GROUP BY s.t_id;

+------+---------------------+
| t_id | AVG(sc.score_score) |
+------+---------------------+
|    1 |             76.6667 |
|    2 |             78.3333 |
+------+---------------------+
2 rows in set (0.00 sec)

把学生表和成绩表内连接，按照老师的id分组，利用AVG函数得出平均分数

还可以加入一个内连接到教师表，根据教师的id得到名字（这里就不写出来了）

查询每个学生的科目数

SELECT st.t_id,st.s_name,COUNT(*) 学生科目数
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
GROUP BY st.s_id;

+------+--------+------------+
| t_id | s_name | 学生科目数 |
+------+--------+------------+
|    1 | 张三   |          3 |
|    1 | 李四   |          3 |
|    2 | 王五   |          3 |
|    2 | 王qi7  |          3 |
+------+--------+------------+
4 rows in set (0.00 sec)

按照学生的 id 来分组，然后计算每组总数

通过 t_score 和 t_student 的内连接，根据学生id 得到学生名字

查询每个科目的平均分、最高分、最低分

SELECT sc.score_subject subject,AVG(sc.score_score) avgScore,MAX(sc.score_score) maxScore,MIN(sc.score_score) minScore
FROM t_score sc
GROUP BY sc.score_subject;

+---------+----------+----------+----------+
| subject | avgScore | maxScore | minScore |
+---------+----------+----------+----------+
| 数学    |  80.0000 |       80 |       80 |
| 英语    |  62.5000 |       70 |       60 |
| 语文    |  90.0000 |       90 |       90 |
+---------+----------+----------+----------+
3 rows in set (0.00 sec)

按照科目来分组，然后计算每组的平均分、最大分，最小分

查询出每个老师(不带老师名字的) 对应科目所有学生的平均分，最低分，最高分，和总分

SELECT st.t_id,sc.score_subject subject,AVG(sc.score_score) avgScore,MAX(sc.score_score) maxScore,MIN(sc.score_score) minScore,SUM(sc.score_score) allScore  # 选取哪些字段？  并且给选取的字段取别名
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
GROUP BY st.t_id,sc.score_subject; # 对老师 、科目 分组

+------+---------+----------+----------+----------+----------+
| t_id | subject | avgScore | maxScore | minScore | allScore |
+------+---------+----------+----------+----------+----------+
|    1 | 数学    |  80.0000 |       80 |       80 |      160 |
|    1 | 英语    |  60.0000 |       60 |       60 |      120 |
|    1 | 语文    |  90.0000 |       90 |       90 |      180 |
|    2 | 数学    |  80.0000 |       80 |       80 |      160 |
|    2 | 英语    |  65.0000 |       70 |       60 |      130 |
|    2 | 语文    |  90.0000 |       90 |       90 |      180 |
+------+---------+----------+----------+----------+----------+
6 rows in set (0.00 sec)

要分两次组，第一次按照老师分组，然后第二次对科目分组

然后，分别计算他们的平均分，最低分，最高分，和总分

接下来把老师的名字加上

#查询出每个老师(要有老师名字) 对应科目所有学生的平均分，最低分，最高分，和总分
SELECT st.t_id,t.t_name,sc.score_subject,AVG(sc.score_score) 平均分,MIN(sc.score_score) 最低分,MAX(sc.score_score) 最高分,SUM(sc.score_score) 总分
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
INNER JOIN t_teacher t
ON st.t_id=t.t_id
GROUP BY st.t_id,sc.score_subject;

+------+--------+---------------+---------+--------+--------+------+
| t_id | t_name | score_subject | 平均分  | 最低分 | 最高分 | 总分 |
+------+--------+---------------+---------+--------+--------+------+
|    1 | 张老师 | 数学          | 80.0000 |     80 |     80 |  160 |
|    1 | 张老师 | 英语          | 60.0000 |     60 |     60 |  120 |
|    1 | 张老师 | 语文          | 90.0000 |     90 |     90 |  180 |
|    2 | 王老师 | 数学          | 80.0000 |     80 |     80 |  160 |
|    2 | 王老师 | 英语          | 65.0000 |     60 |     70 |  130 |
|    2 | 王老师 | 语文          | 90.0000 |     90 |     90 |  180 |
+------+--------+---------------+---------+--------+--------+------+
6 rows in set (0.00 sec)

和上边的相比，这部分代码就是多了，一个按照老师id，对老师表和学生表的内连接。

然后输出老师的名字

查询出每个老师(要有老师名字) 对应科目所有学生的平均分，最低分，最高分，和总分,并且按照平均分降序排序

SELECT r.*
FROM( # 子查询开始
SELECT st.t_id,t.t_name,sc.score_subject,AVG(sc.score_score) avg_score,MIN(sc.score_score) 最低分,MAX(sc.score_score) 最高分,SUM(sc.score_score) 总分
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
INNER JOIN t_teacher t
ON st.t_id=t.t_id
GROUP BY st.t_id,sc.score_subject
)r # 子查询结束，并取别名为 r
ORDER BY r.avg_score DESC;
#### 注意： 作为二维表使用的查询结果必须有表别名 ，这里是 r

+------+--------+---------------+-----------+--------+--------+------+
| t_id | t_name | score_subject | avg_score | 最低分 | 最高分 | 总分 |
+------+--------+---------------+-----------+--------+--------+------+
|    1 | 张老师 | 语文          |   90.0000 |     90 |     90 |  180 |
|    2 | 王老师 | 语文          |   90.0000 |     90 |     90 |  180 |
|    1 | 张老师 | 数学          |   80.0000 |     80 |     80 |  160 |
|    2 | 王老师 | 数学          |   80.0000 |     80 |     80 |  160 |
|    2 | 王老师 | 英语          |   65.0000 |     60 |     70 |  130 |
|    1 | 张老师 | 英语          |   60.0000 |     60 |     60 |  120 |
+------+--------+---------------+-----------+--------+--------+------+
6 rows in set (0.00 sec)

相比上一个问题，就是多了一个，按照平均分降序

那么，这里使用子查询，将上一个查询结果，作为子查询，并且取别名为 r，对r 按照平均分降序排序

也可以不使用子查询,这两种写法是等价的

# 另一种写法
SELECT st.t_id,t.t_name,sc.score_subject,AVG(sc.score_score) avg_score,MIN(sc.score_score) 最低分,MAX(sc.score_score) 最高分,SUM(sc.score_score) 总分
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
INNER JOIN t_teacher t
ON st.t_id=t.t_id
GROUP BY st.t_id,sc.score_subject
ORDER BY avg_score DESC;

在分组后我们也可以对数据进行筛选

比如，选出每组里面的，平均分大于80的，注意，这里要使用HAVING，而不是where

### 对于分组的筛选，必须使用HAVING
SELECT st.t_id,t.t_name,sc.score_subject,AVG(sc.score_score) avg_score,MIN(sc.score_score) 最低分,MAX(sc.score_score) 最高分,SUM(sc.score_score) 总分
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
INNER JOIN t_teacher t
ON st.t_id=t.t_id
GROUP BY st.t_id,sc.score_subject
HAVING avg_score > 80  # 分组筛选条件
ORDER BY avg_score DESC;


####  对于子查询，使用where
SELECT r.*
FROM( # 子查询开始
SELECT st.t_id,t.t_name,sc.score_subject,AVG(sc.score_score) avg_score,MIN(sc.score_score) 最低分,MAX(sc.score_score) 最高分,SUM(sc.score_score) 总分
FROM t_score sc
INNER JOIN t_student st
ON st.s_id=sc.stu_id
INNER JOIN t_teacher t
ON st.t_id=t.t_id
GROUP BY st.t_id,sc.score_subject
)r # 子查询结束，并取别名为 r
WHERE r.avg_score > 80   # 子查询，没有分组，这里使用where筛选
ORDER BY r.avg_score DESC;

+------+--------+---------------+-----------+--------+--------+------+
| t_id | t_name | score_subject | avg_score | 最低分 | 最高分 | 总分 |
+------+--------+---------------+-----------+--------+--------+------+
|    1 | 张老师 | 语文          |   90.0000 |     90 |     90 |  180 |
|    2 | 王老师 | 语文          |   90.0000 |     90 |     90 |  180 |
+------+--------+---------------+-----------+--------+--------+------+
2 rows in set (0.00 sec)

在分组查询中，必须使用HAVING来筛选，不可以使用WHERE

涉及到的知识点

多表连接查询（内连接），没有用到（左外连接、右外连接）
分组查询（GROPU BY ），筛选使用HAVING关键字，不可以使用WHERE
分组查询里面，涉及到的组函数： AVG，MAX，MIN，COUNT，这是最常用的，还有一些其他的没有用到
排序（ORDER BY），DESC 降序，ASC升序，默认升序
子查询，一个查询的结果作为另一个查询的输入,作为二维表使用的查询结果必须有表别名
再说最基本的查询格式： SELECT [要查询的字段] from [表] WHERE [筛选的条件]
对查询结果取别名，对表取别名

具体的知识点，概念，就不在这里详细罗列了，很多地方都有

练习用的数据表

/*
SQLyog Ultimate v8.32 
MySQL - 5.7.19 : Database - student
*********************************************************************
*/
CREATE TABLE `t_score` (
  `scorre_id` int(11) NOT NULL AUTO_INCREMENT,
  `score_score` int(3) DEFAULT NULL,
  `score_subject` varchar(50) DEFAULT NULL,
  `stu_id` int(9) DEFAULT NULL,
  PRIMARY KEY (`scorre_id`),
  KEY `FK_t_score` (`stu_id`),
  CONSTRAINT `FK_t_score` FOREIGN KEY (`stu_id`) REFERENCES `t_student` (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

/*Data for the table `t_score` */

insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (3,90,'语文',2);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (4,80,'数学',2);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (5,60,'英语',2);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (6,90,'语文',1);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (7,80,'数学',1);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (8,60,'英语',1);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (9,90,'语文',3);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (10,80,'数学',3);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (11,60,'英语',3);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (12,90,'语文',4);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (13,80,'数学',4);
insert  into `t_score`(`scorre_id`,`score_score`,`score_subject`,`stu_id`) values (15,70,'英语',4);

/*Table structure for table `t_student` */

CREATE TABLE `t_student` (
  `s_id` int(9) NOT NULL AUTO_INCREMENT,
  `s_name` varchar(50) DEFAULT NULL,
  `t_id` int(9) DEFAULT NULL,
  PRIMARY KEY (`s_id`),
  KEY `FK_t_student` (`t_id`),
  CONSTRAINT `FK_t_student` FOREIGN KEY (`t_id`) REFERENCES `t_teacher` (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

/*Data for the table `t_student` */

insert  into `t_student`(`s_id`,`s_name`,`t_id`) values (1,'张三',1);
insert  into `t_student`(`s_id`,`s_name`,`t_id`) values (2,'李四',1);
insert  into `t_student`(`s_id`,`s_name`,`t_id`) values (3,'王五',2);
insert  into `t_student`(`s_id`,`s_name`,`t_id`) values (4,'王qi7',2);
insert  into `t_student`(`s_id`,`s_name`,`t_id`) values (5,'钱一',1);

/*Table structure for table `t_teacher` */

CREATE TABLE `t_teacher` (
  `t_id` int(9) NOT NULL AUTO_INCREMENT,
  `t_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `t_teacher` */

insert  into `t_teacher`(`t_id`,`t_name`) values (1,'张老师');
insert  into `t_teacher`(`t_id`,`t_name`) values (2,'王老师');

参考链接

https://blog.youkuaiyun.com/plg17/article/details/78758593

https://zhuanlan.zhihu.com/p/46925457

https://blog.youkuaiyun.com/J080624/article/details/72909945

ULL,
PRIMARY KEY (t_id)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table t_teacher */

insert into t_teacher(t_id,t_name) values (1,‘张老师’);
insert into t_teacher(t_id,t_name) values (2,‘王老师’);




## 参考链接

https://blog.youkuaiyun.com/plg17/article/details/78758593

https://zhuanlan.zhihu.com/p/46925457

https://blog.youkuaiyun.com/J080624/article/details/72909945

https://www.runoob.com/mysql/mysql-order-by.html