数据表去重

最新推荐文章于 2022-07-24 23:55:22 发布

原创最新推荐文章于 2022-07-24 23:55:22 发布 · 151 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#移动开发 #数据库

oracle 专栏收录该内容

6 篇文章

订阅专栏



-- 查找表中多余的重复记录(多个字段)

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)


-- 删除表中多余的重复记录(多个字段)，只留有id最小的记录
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
这样的话就把所有重复的都删除了。

数据表 去重

数据表去重