sql如何先排序再去重 |
您所在的位置:网站首页 › SQL排序前五 › 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最大的那一行了。 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |