min/max 与group by连用（MySQL8.0.19）

本是古典何须时尚 2023-10-04 13:19 7阅读 0赞

MySQL使用的版本：8.0.19；  
准备2张表：student，score；

student表

*  stuid：学号
 *  classid：班级id；
 *  stuName：学生名字

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2_size_16_color_FFFFFF_t_70_pic_center]  
score表：

*  courseid：课程id
 *  stuid：学号
 *  score：分数  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2_size_16_color_FFFFFF_t_70_pic_center 1]

现在查询各班各科成绩最高分；分组按照：班级和科目来分：

select  c.courseid,classid,max(c.score) MAXscore
    from student s,score c 
    where s.stuId=c.stuid 
    group by s.classId,c.courseid

结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2_size_16_color_FFFFFF_t_70_pic_center 2]

结果正确返回了分组中的最大值；这样的写法是没有问题的，得到了预期结果；我们以下面的写法可以得到正确的结果：

select a,b,聚合函数(c) from table_name where m=n group by a,b
    
    聚合函数：min/max/count/avg/sum

我们在上述的要求上增加一个字段，得到最高分的学生号：stuid；

可以直接添加stuid吗：

select  c.courseid,s.stuid,classid,max(c.score) MAXscore 
    from student s,score c 
    where s.stuId=c.stuid 
    group by s.classId,c.courseid
    
    ------------------------------------------
    预期的结果：（从上面score表中的数据预测结果）
    
    courseid	stuid	classid		MAXscore 
    	1		 3		  1			 80
    	1		 6		  2			 100
    	1		 12		  3			 124
    	1		 14		  4			 188

实际结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2_size_16_color_FFFFFF_t_70_pic_center 3]  
预期与实际结果不符，写法有问题。原因是什么呢？我们知道使用group by分组时，对于有相同的分组字段行会合并成一行，这个时候，如果查询的字段中有非分组字段，非分组字段的数据会读取合并的第一行；  
再举一个简单列子：单看score表，以courseid分组，求MaxScore;

select courseid ,stuid,max(score) from score group by courseid ;
    正确的结果：
    courseid		stuid			score 
    	1			  14             188

实际结果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2_size_16_color_FFFFFF_t_70_pic_center 4]  
stuid：非分组字段，他的结果是取得合并时的第一行数据：1；

如果想要得到预期结果怎么办呢？：  
思路：先将分组的courseid，max(score)查出来，存到一张临时表t中，再利用max,courseid的值再从score表中查询

select s.courseid ,s.stuid, s.score 
    from score s,
    (select courseid,max(score) score from score group by courseid ) t 
    where  
    s.courseid=t.courseid and t.score=s.score

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20210513014257980.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20210513014340216.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20210513033450347.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20210513041835104.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2_size_16_color_FFFFFF_t_70_pic_center 4]: https://img-blog.csdnimg.cn/20210513045203192.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTUwOTg2,size_16,color_FFFFFF,t_70#pic_center