看网上很多说法,觉得都不好(不详细或者跟着做没法成功),我自己琢磨出一个详细的解释。从分组中选出最大(小)值是面试中常用的考题。
跟着我的步骤来:
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 max(score) as score,name from ha group by name;
±------±-----+
| score | name |
±------±-----+
| 3 | jack |
| 3 | json |
±------±-----+
其实上面那样已经足够把不同组的最大值选出来了,但是却因为group by name,所以只能除了max(score)之外额外显示name,但是id无法显示了,除非像下面那样,但是我想要的是唯一对应的id,所以必须通过用ha表和上面的表对应链接来才能显示完全。( as score是给max(score)起名字为score的意思,对后面链接有用)
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表的内容。