MySQL从不同分组中选出最大（小）值

最新推荐文章于 2023-02-22 14:08:21 发布

babyjustsaidyes

最新推荐文章于 2023-02-22 14:08:21 发布

阅读量1.1k

点赞数

分类专栏： python具体知识点讲解

本文链接：https://blog.csdn.net/weixin_43256057/article/details/85331043

版权

python具体知识点讲解专栏收录该内容

58 篇文章 1 订阅

订阅专栏

看网上很多说法，觉得都不好（不详细或者跟着做没法成功），我自己琢磨出一个详细的解释。从分组中选出最大（小）值是面试中常用的考题。
跟着我的步骤来：
mysql -uxxx -pxxx（2个xxx分别是账号和密码）
进入到某个数据库后（use xxx;）

mysql> create table ha
     (
    `id` int not null auto_increment,
     `name` varchar(20) not null default '',
     `score` int not null default 0,
     primary key(`id`)
     )engine=InnoDB CHARSET=UTF8;

我不知道这个engine=InnoDB CHARSET=UTF8有什么用，不过没有它也不影响后续操作的。

mysql> insert into ha values (0,‘json’,1),(0,‘json’,2),(0,‘json’,3),(0,‘jack’,1),(0,‘jack’,2),(0,‘jack’,3);

mysql> select * from ha;
±—±-----±------+
| id | name | score |
±—±-----±------+
| 1 | json | 1 |
| 2 | json | 2 |
| 3 | json | 3 |
| 4 | jack | 1 |
| 5 | jack | 2 |
| 6 | jack | 3 |
±—±-----±------+

mysql> select group_concat(id),name,max(score) from ha group by name;
±-----------------±-----±-----------+
| group_concat(id) | name | max(score) |
±-----------------±-----±-----------+
| 4,5,6 | jack | 3 |
| 1,2,3 | json | 3 |
±-----------------±-----±-----------+

mysql> select * from ha as a left join (select name,max(score) as score from ha group by name) as b on a.score=b.score;
±—±-----±------±-----±------+
| id | name | score | name | score |
±—±-----±------±-----±------+
| 1 | json | 1 | NULL | NULL |
| 2 | json | 2 | NULL | NULL |
| 3 | json | 3 | jack | 3 |
| 3 | json | 3 | json | 3 |
| 4 | jack | 1 | NULL | NULL |
| 5 | jack | 2 | NULL | NULL |
| 6 | jack | 3 | jack | 3 |
| 6 | jack | 3 | json | 3 |
±—±-----±------±-----±------+

left join 左链接会以左边的为基本全部显示，右边链接上的表相比左边的不够则补全null，多出的就不显示，这里明显不适用。所以用内链接inner join让右边的表不够直接不显示就更接近了。

mysql> select * from ha as a inner join (select name,max(score) as score from ha group by name) as b on a.score=b.score;
±—±-----±------±-----±------+
| id | name | score | name | score |
±—±-----±------±-----±------+
| 3 | json | 3 | jack | 3 |
| 3 | json | 3 | json | 3 |
| 6 | jack | 3 | jack | 3 |
| 6 | jack | 3 | json | 3 |
±—±-----±------±-----±------+

只基于 on a.score=b.score会因为右边的表b：
±------±-----+
| score | name |
±------±-----+
| 3 | jack |
| 3 | json |
±------±-----+
都显示分数为3，所以ha表a：
±—±-----±------+
| id | name | score |
±—±-----±------+
| 1 | json | 1 |
| 2 | json | 2 |
| 3 | json | 3 |
| 4 | jack | 1 |
| 5 | jack | 2 |
| 6 | jack | 3 |
±—±-----±------+

id=3的json就可以对应拼接分数都为3的jack和json，id=6的jack也如此，因此不能一一对应。

mysql> select * from ha as a inner join (select name,max(score) as score from ha group by name) as b on a.score=b.score and a.name=b.name;
±—±-----±------±-----±------+
| id | name | score | name | score |
±—±-----±------±-----±------+
| 3 | json | 3 | json | 3 |
| 6 | jack | 3 | jack | 3 |
±—±-----±------±-----±------+

这样a.score=b.score and a.name=b.name，必须名字相等的才对应拼接就可以把相同组的拼到一起，同组里面分数又要对应才能拼一起，那就只能是本组的最大值才可以了。

mysql> select a.* from ha as a inner join (select name,max(score) as score from ha group by name) as b on a.score=b.score and a.name=b.name;
±—±-----±------+
| id | name | score |
±—±-----±------+
| 3 | json | 3 |
| 6 | jack | 3 |

只显示a表的内容。

mysql> select a.* from ha a inner join (select name,max(score) score from ha group by name) b on a.name=b.name and a.score=b.score;
±—±-----±------+
| id | name | score |
±—±-----±------+
| 3 | json | 3 |
| 6 | jack | 3 |
±—±-----±------+

没有 ha as a 的as也照样可以起名字的，不过我觉得有个as可能更清晰易懂。

。

练习2：employee表的departmentID和department表的id是一一对应，要把employee表的各部门里最高薪资的员工的相应所有信息都展示出来。
mysql> select * from employee;
±—±-------±-------±-------------+
| id | name | salary | departmentID |
±—±-------±-------±-------------+
| 1 | 陈名 | 3000 | 1 |
| 2 | 王单 | 4652 | 2 |
| 3 | janny | 5000 | 1 |
| 4 | tonny | 65000 | 2 |
| 5 | mastar | 5500 | NULL |
±—±-------±-------±-------------+
mysql> select * from department;
±—±-----------+
| id | department |
±—±-----------+
| 1 | IT |
| 2 | designed |
±—±-----------+

答：思路：一次性链接2表并group by肯定不可以展示全部信息，必须先执行前面的操作展示出各组最大的值，然后再用employee表再链接前者并一一对应，从而把各组最大值的对应在employee表中的全部信息都展示出来。
mysql> select d.id,max(salary) salary from employee e left join department d on e.departmentID=d.id group by d.id;
±-----±-------+
| id | salary |
±-----±-------+
| NULL | 5500 |
| 1 | 5000 |
| 2 | 65000 |
±-----±-------+

这样已经很接近了，但不能展示全部信息，可以展示group_concat(name)等,但不符合题目要求。

mysql> select e.* from employee as e inner join(select d.id,max(salary) salary from employee e left join department d on e.departmentID=d.id group by d.id) d on e.departmentID=d.id and e.salary=d.salary;
±—±------±-------±-------------+
| id | name | salary | departmentID |
±—±------±-------±-------------+
| 3 | janny | 5000 | 1 |
| 4 | tonny | 65000 | 2 |
±—±------±-------±-------------+

通过inner join链接2表并且on e.departmentID=d.id and e.salary=d.salary，让各条件一一对应才显示，从而把需要的才现实出来。成功。

可能有人会想写以下的语句是否也可以吗：
mysql> select e.* from employee as e inner join(select d.id,max(salary) salary from employee e left join department d on e.departmentID=d.id group by d.id) d on e.salary=d.salary group by d.id;

on e.salary=d.salary group by d.id，对应拼接后再以id分组不就既分组而且让每一组的相同的salary唯一对应显示了。其实这样纯粹给自己找事，因为只要分组了就别指望能显示符合要求的记录的所有的信息，因为一旦分组，每组里面的不同字段都会有多个对应的值，比如departmentID=1的组的name字段就有janny和陈名，需要group_concat(name)才能显示全部，但也就无法一一对应显示。只有通过on e.departmentID=d.id and e.salary=d.salary这种多个属性值一一对应才能进行筛选的同时也能显示每行记录的所有信息。