MySQL取出表中排序数据且不重复删除重复数据(1/2)-百分百源码网

MySQL取出表中排序数据且不重复删除重复数据(1/2)

发布时间：01/15 来源：浏览：关键词：

在mysql中出现重复的数据时我们有需要进行处理，下面我来给大家介绍关于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大小排序的。

例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;

/* 插入测试数据 */
INSERT INTO `t1`(`name`,`add`) VALUES
('abc',"123"),
('abc',"123"),
('abc',"321"),
('abc',"123"),
('xzy',"123"),
('xzy',"456"),
('xzy',"456"),
('xzy',"456"),
('xzy',"789"),
('xzy',"987"),
('xzy',"789"),
('ijk',"147"),
('ijk',"147"),
('ijk',"852"),
('opq',"852"),
('opq',"963"),
('opq',"741"),
('tpk',"741"),
('tpk',"963"),
('tpk',"963"),
('wer',"546"),
('wer',"546"),
('once',"546");

SELECT * FROM `t1`;
+----+------+-----+
| id | name | add |
+----+------+-----+
| 1 | abc | 123 |
| 2 | abc | 123 |
| 3 | abc | 321 |
| 4 | abc | 123 |
| 5 | xzy | 123 |
| 6 | xzy | 456 |
| 7 | xzy | 456 |
| 8 | xzy | 456 |
| 9 | xzy | 789 |
| 10 | xzy | 987 |
| 11 | xzy | 789 |
| 12 | ijk | 147 |
| 13 | ijk | 147 |
| 14 | ijk | 852 |
| 15 | opq | 852 |
| 16 | opq | 963 |
| 17 | opq | 741 |
| 18 | tpk | 741 |
| 19 | tpk | 963 |
| 20 | tpk | 963 |
| 21 | wer | 546 |
| 22 | wer | 546 |
| 23 | once | 546 |
+----+------+-----+
rows in set (0.00 sec)

查找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)