Hot For Coding
MySQL取出表中排序数据且不重复

今日做一个功能需求,从表中排序取出前十个且数据不一致的数据,如下列a表中的值有

mysql> select * from a;
+----+----------+
| id | user     |
+----+----------+
|  1 | zhangsan |
|  2 | lisi     |
|  3 | wangwu   |
|  4 | zhangsan |
|  5 | zhaosi   |
|  6 | wangwu   |
|  7 | lisi     |
|  8 | lisi     |
|  9 | zhaosi   |
+----+----------+
9 rows in set (0.00 sec)

我们需要取出id最大的前四位,且user不能一致的信息,按照以上信息以及需求,我们要搜索出结果为

zhaosi
lisi
wangwu
zhangsan

不能按照普通的做法,如:

mysql> select * from a order by id desc limit 4;
+----+--------+
| id | user   |
+----+--------+
|  9 | zhaosi |
|  8 | lisi   |
|  7 | lisi   |
|  6 | wangwu |
+----+--------+
4 rows in set (0.00 sec)

这样搜索出来的有重复值,得使用distinct关键字

mysql> select distinct user from a order by id desc limit 4; 
+----------+
| user     |
+----------+
| zhaosi   |
| wangwu   |
| lisi     |
| zhangsan |
+----------+
4 rows in set (0.00 sec)

其实应该是lisi与wangwu互换一下才是比较理想的,因为lisi最大的ID是8,而wangwu最大的ID是6,可能是lisi有一个ID为2导致的,我们把ID为2的删除,在来试试

mysql> delete from a where id=2;
Query OK, 1 row affected (0.02 sec)
 
mysql> select * from a;
+----+----------+
| id | user     |
+----+----------+
|  1 | zhangsan |
|  3 | wangwu   |
|  4 | zhangsan |
|  5 | zhaosi   |
|  6 | wangwu   |
|  7 | lisi     |
|  8 | lisi     |
|  9 | zhaosi   |
+----+----------+
8 rows in set (0.00 sec)
 
mysql> select distinct user from a order by id desc limit 4; 
+----------+
| user     |
+----------+
| lisi     |
| zhaosi   |
| wangwu   |
| zhangsan |
+----------+
4 rows in set (0.00 sec)

结果正是由于前边有较低的ID记录影响了排序。

虽然这条语句能搜索正确的效果,但可能排序不是那么理想,也就是ID最大的前四位能搜索出来,但在这四位数据里并不是按照ID大小排序的。

TITLE: MySQL取出表中排序数据且不重复

LINK: https://www.qttc.net/198_mysql_unique_sort.html

NOTE: 转载内容请注明出处