sql如何先排序再去重

场景

有一张得分表(score)，记录了用户每次的得分，同一个人可能有多个得分。

id	name	score
1	tom	45
2	jack	78
3	tom	34
.	.	.

需求：找出分数最高的前5个人。

SQL1

首先我们写个最简单的sql：

select 
    id, name, score
from 
    score
order by 
    score desc
limit 5;

如果sql这样写，结果可能是：

id	name	score
2	jack	78
1	tom	45
3	tom	34

排序了，但是没有去重

SQL2

那么我们加上去重：

select 
    distinct name
from 
    score
order by 
    score desc
limit 5;

首先第一点是这个sql未必能执行。在一些数据库版本，这个sql可以被执行，在一些版本则会提示你order by的字段必须在distinct中存在(见SQL3)。

但是即使能执行，这个sql也得不到预期结果。原因是distinct优先于order by 被数据库执行。

在执行distinct name的时候，如上文中的数据。是取id=1的数据，还是id=3的数据呢？其实这是数据库自行决定的。因此，可能会不正确选择数据。

比如真的执行这个sql，可能去重的结果是：

id	name	score
2	jack	78
3	tom	34

然后再执行一个order by，就会认为第一名是jack78分，第二名是tom34分。然而其实tom应该是45分，这个45分就在数据库执行distinct的时候被错误的丢弃了，毕竟先执行distinct的时候不知道你到底要哪个数据。

SQL3

那么我们把score加入select中呢？

select 
    distinct name, score
from 
    score
order by 
    score desc
limit 5;

很明显，这样写的执行结果和我们预期不符。因为如果写：distinct name,score实际上是对name和score一起去重。比如name都是jack，score都是45。那么这行就会被去掉。

但是问题是正因为把score当做去重的条件了。所以对于同名的人，比如都叫tom，会因为其有两个分数，导致不能被去重，从而保留两行记录。结果就是好像没有去重。

SQL4

那我不用distinct，用group by进行去重可以吗？

select 
    name
from 
    score
group by
    name
order by 
    score desc
limit 5;

也不行，因为在group by的时候，数据库还是不知道对两行name一样的数据，究竟应该留下哪一行。

SQL5

正确的写法：

select 
    name
from 
    score
group by
    name
order by 
    max(score) desc
limit 5;

这样写，在执行group by的时候，数据库就知道要保留score最大的那一行了。

转载于:https://www.cnblogs.com/dsj2016/p/10679366.html