今日做一个功能需求，从表中排序取出前十个且数据不一致的数据，如下列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大小排序的。
例1测试数据
| 代码如下 | 复制代码 |
|
/* 表结构 */ DROP TABLE IF EXISTS `t1`; CREATE TABLE IF NOT EXISTS `t1`( `id` INT(1) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) NOT NULL, `add` VARCHAR(20) NOT NULL, PRIMARY KEY(`id`) )Engine=InnoDB; /* 插入测试数据 */ SELECT * FROM `t1`;
|
|
查找id最小的重复数据(只查找id字段)
| 代码如下 | 复制代码 |
|
/* 查找id最小的重复数据(只查找id字段) */ SELECT DISTINCT MIN(`id`) AS `id` FROM `t1` GROUP BY `name`,`add` HAVING COUNT(1) > 1; +------+ | id | +------+ | 1 | | 12 | | 19 | | 21 | | 6 | | 9 | +------+ rows in set (0.00 sec) |
|
查找所有重复数据
| 代码如下 | 复制代码 |
|
/* 查找所有重复数据 */ SELECT `t1`.* FROM `t1`,( SELECT `name`,`add` FROM `t1` GROUP BY `name`,`add` HAVING COUNT(1) > 1 ) AS `t2` WHERE `t1`.`name` = `t2`.`name` AND `t1`.`add` = `t2`.`add`; +----+------+-----+ | id | name | add | +----+------+-----+ | 1 | abc | 123 | | 2 | abc | 123 | | 4 | abc | 123 | | 6 | xzy | 456 | | 7 | xzy | 456 | | 8 | xzy | 456 | | 9 | xzy | 789 | | 11 | xzy | 789 | | 12 | ijk | 147 | | 13 | ijk | 147 | | 19 | tpk | 963 | | 20 | tpk | 963 | | 21 | wer | 546 | | 22 | wer | 546 | +----+------+-----+ rows in set (0.00 sec)
|
|
查找除id最小的数据外的重复数据
| 代码如下 | 复制代码 |
|
/* 查找除id最小的数据外的重复数据 */ SELECT `t1`.* FROM `t1`,( SELECT DISTINCT MIN(`id`) AS `id`,`name`,`add` FROM `t1` GROUP BY `name`,`add` HAVING COUNT(1) > 1 ) AS `t2` WHERE `t1`.`name` = `t2`.`name` AND `t1`.`add` = `t2`.`add` AND `t1`.`id` <> `t2`.`id`; +----+------+-----+ | id | name | add | +----+------+-----+ | 2 | abc | 123 | | 4 | abc | 123 | | 7 | xzy | 456 | | 8 | xzy | 456 | | 11 | xzy | 789 | | 13 | ijk | 147 | | 20 | tpk | 963 | | 22 | wer | 546 | +----+------+-----+ rows in set (0.00 sec)
|
|
首页 1 2 末页