mysql 去除重复记录解决办法

mysql 去除重复记录

有如下sql

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `count`;

CREATE TABLE `count` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`a1` int(11) NOT NULL,

`b1` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

INSERT INTO `count` VALUES (‘1’, ‘1’, ‘1’);

INSERT INTO `count` VALUES (‘2’, ‘7’, ‘2’);

INSERT INTO `count` VALUES (‘3’, ‘2’, ‘1’);

INSERT INTO `count` VALUES (‘4’, ‘6’, ‘2’);

INSERT INTO `count` VALUES (‘5’, ‘5’, ‘3’);

INSERT INTO `count` VALUES (‘6’, ‘2’, ‘0’);

INSERT INTO `count` VALUES (‘7’, ‘2’, ‘4’);

希望获取a1字段值和b1字段值都不重复的记录

返回的正确结果应该是

id a1 b1

‘5’ ‘5’ ‘3’

SQL code

mysql> select * from `count`;
+----+----+----+
| id | a1 | b1 |
+----+----+----+
|  1 |  1 |  1 |
|  2 |  7 |  2 |
|  3 |  2 |  1 |
|  4 |  6 |  2 |
|  5 |  5 |  3 |
|  6 |  2 |  0 |
|  7 |  2 |  4 |
+----+----+----+
7 rows in set (0.00 sec)

mysql> select * from `count` t
    -> where not exists (select 1 from `count` where (a1=t.a1 or b1=t.b1) and id!=t.id);
+----+----+----+
| id | a1 | b1 |
+----+----+----+
|  5 |  5 |  3 |
+----+----+----+
1 row in set (0.00 sec)

mysql>

相关文章：

你感兴趣的文章：

标签云：