-- 查找表中多余的重复记录(多个字段)
SELECT * FROM tb_news1 a
WHERE (a.Id,a.TITLE,a.SOURCE,a.DATE) IN
(SELECT a.Id,a.TITLE,a.SOURCE,a.DATE FROM tb_news1 a GROUP BY a.Id,a.TITLE,a.SOURCE,a.DATE HAVING COUNT(1) > 1)
-- 删除表中多余的重复记录(多个字段),只留有rowid最小的记录
DELETE FROM tb_news1 a WHERE
(a.Id,a.TITLE,a.SOURCE,a.DATE) IN
(SELECT a.Id,a.TITLE,a.SOURCE,a.DATE FROM tb_news1 a GROUP BY a.Id,a.TITLE,a.SOURCE,a.DATE HAVING COUNT(1) > 1)
AND a.Id NOT IN (SELECT MIN(a.Id) FROM tb_news1 a GROUP BY a.Id,a.TITLE,a.SOURCE,a.DATE HAVING COUNT(1)>1 )
-- ================ test data ======================
DROP TABLE IF EXISTS tb_news1;
CREATE TABLE tb_news1 (
Id INT(11) NOT NULL ,
TITLE VARCHAR(255) DEFAULT NULL COMMENT '标题',
CONTENT VARCHAR(1000) DEFAULT NULL COMMENT '内容',
SOURCE VARCHAR(255) DEFAULT NULL COMMENT '新闻来源',
DATE DATETIME DEFAULT NULL COMMENT '新闻日期'
-- ,
-- PRIMARY KEY (Id)
) ENGINE=INNODB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
INSERT INTO tb_news1 VALUES (1,'谷歌发布Android 4.0.3内核源代码','Google','iteye','2011-12-20');
INSERT INTO tb_news VALUES (2,'谷歌发布Android 4.0.3内核源代码','Google','iteye','2011-12-20');
INSERT INTO tb_news VALUES (3,'Ubuntu 将从用户电脑移除 JDK','Ubuntu ','baidu','2011-12-20');
INSERT INTO tb_news VALUES (4,'谷歌发布Android 4.0.3内核源代码','Google','iteye','2011-12-20');
INSERT INTO tb_news VALUES (5,'Ubuntu 将从用户电脑移除 JDK','Ubuntu ','baidu','2011-12-20');
INSERT INTO tb_news1 VALUES (6,'Ubuntu 将从用户电脑移除 JDK','Ubuntu ','baidu','2011-12-20');
--
INSERT INTO tb_news1 VALUES (1,'谷歌发布Android 4.0.3内核源代码','Google','iteye','2011-12-20');
INSERT INTO tb_news1 VALUES (6,'Ubuntu 将从用户电脑移除 JDK','Ubuntu ','baidu','2011-12-20');
SELECT * FROM tb_news1;
-- =================================
-- ---
1。用rowid方法
据据oracle带的rowid属性,进行判断,是否存在重复,语句如下:
查数据:
SELECT * FROM table1 a WHERE rowid !=(SELECT MAX(rowid)
FROM table1 b WHERE a.name1=b.name1 AND a.name2=b.name2……)
删数据:
DELETE FROM table1 a WHERE rowid !=(SELECT MAX(rowid)
FROM table1 b WHERE a.name1=b.name1 AND a.name2=b.name2……)
2.group BY方法
查数据:
SELECT COUNT(num), MAX(NAME) FROM student --列出重复的记录数,并列出他的NAME属性
GROUP BY num
HAVING COUNT(num) >1 --按num分组后找出表中num列重复,即出现次数大于一次
删数据:
DELETE FROM student
GROUP BY num
HAVING COUNT(num) >1
这样的话就把所有重复的都删除了。
mysql 去重
最新推荐文章于 2020-05-26 16:37:04 发布