问题描述
我有一个名字
的MySQL表,它由两个字段组成: name
和秩
。 名称
值不是唯一可以有多个匹配。
问题:我想选择记录,按 例如: name
,但如果有多个名称
,则具有最高排名
$ b
$ b本1
本2
通常会返回:
$ b Tom 2 p>
本1
我需要:
Tom 2
本2
由于有两个Bens,但第二个有更高的等级。
看起来,MySQL分组取第一个名字并忽略其余部分。
如何在group通过,所以我可以说,应该采取哪个记录,如果有多个相同的记录 name
?
您需要,称为 max
:
从名称
中选择名称,最大(等级)
按名称分组;
通过这种方式,您将检索所有不同的名称,每个名称都与其最大排名相关联。
I have a MySQL table of names
, which consists of two fields: name
and rank
. The name
value is not unique can have multiple matches.
The problem: I want to select records, grouped by name
, but if there are more than one name
, the one with the highest rank
should be taken.
An example:
Tom 2
Ben 1
Ben 2
Usually returns:
Tom 2
Ben 1
I need:
Tom 2
Ben 2
Since there are two Bens, but the second one with a higher rank.
It seems, that MySQL grouping takes the first name and ignores the rest.
How do I order records inside "group by", so I could say which record should be taken, if there is more than one with the same name
?
You need an aggregate function called max
:
select name, max(rank)
from names
group by name;
This way you will retrieve all distinct names, each one associated with its max rank.
这篇关于MySQL:“排序”在“group by”之内的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!