sql排序后去重

逃离我推掉我的手 2022-10-09 12:18 141阅读 0赞

场景

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

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>score</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>tom</td> 
   <td>45</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>jack</td> 
   <td>78</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>tom</td> 
   <td>34</td> 
  </tr> 
  <tr> 
   <td>.</td> 
   <td>.</td> 
   <td>.</td> 
  </tr> 
 </tbody> 
</table>

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

SQL1

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

1.  `select`
2.  `id, name, score`
3.  `from`
4.  `score`
5.  `order by`
6.  `score desc`
7.  `limit 5;`

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

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>score</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>jack</td> 
   <td>78</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>tom</td> 
   <td>45</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>tom</td> 
   <td>34</td> 
  </tr> 
 </tbody> 
</table>

排序了，但是没有去重

SQL2

那么我们加上去重：

1.  `select`
2.  `distinct name`
3.  `from`
4.  `score`
5.  `order by`
6.  `score desc`
7.  `limit 5;`

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

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

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

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

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>score</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>jack</td> 
   <td>78</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>tom</td> 
   <td>34</td> 
  </tr> 
 </tbody> 
</table>

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

SQL3

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

1.  `select`
2.  `distinct name, score`
3.  `from`
4.  `score`
5.  `order by`
6.  `score desc`
7.  `limit 5;`

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

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

SQL4

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

1.  `select`
2.  `name`
3.  `from`
4.  `score`
5.  `group by`
6.  `name`
7.  `order by`
8.  `score desc`
9.  `limit 5;`

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

SQL5

正确的写法：