mysql查询的一个问题

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.csdn.net/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)

把学生表和成绩表