删除重复记录

[amdgaming]

先有一张表，有一个字段为code

里面有很多code重复的，现在要求删除 重复 大于1 的

只保留 id 最大的 记录行 ，其他删除了

如果不重复的不删除

[cowboyhn]

可参考这个帖子：
http://topic.csdn.net/u/20120229/20/4784904e-8bf0-4563-8aa1-c6b9816e6757.html

[xpingping]

delete from table where rowid in(
select rowid from(
select rowid,
row_number() over(partition by code order by id desc) rn 
from table 
where rn>1))

[ICE-word]

delete from a where a.rowid < (select max(b.rowid) from a b where a.id = b.id)

[raymonshi]

delete table a where not exists 
  (select 1 from table group by code having a.id = max(id)
  )

[yinan9]

DELETE FROM T A WHERE ID < (SELECT MAX(ID) FROM T B WHERE B.CODE = A.CODE);

[大明湖畔_帅锅]

[Quote=引用 2 楼 xpingping 的回复:]

SQL code
delete from table where rowid in(
select rowid from(
select rowid,
row_number() over(partition by code order by id desc) rn
from table
where rn>1))
[/Quote]

想的一样

[Felixzhaowenzhong]

2楼有语法错误。纠正一下下delete from tb where id in (select id from (select id,ROW_NUMBER() over(partition by code order by id desc)as rn from tb)a where rn>1)

[Felixzhaowenzhong]

[lxyzxq2008]

delete from table where id not in(
select id from(
select id,code row_number()over(partition by code order by id desc) rn
from table
)
where rn =1
)

[walkman_22]

可以先建个临时表，把最大的ID和CODE插进去。SQL语句应该很简单，加个子查询就可以。
然后把表清空，把刚才临时表中的数据再导回来。

[yinan9]

你这里的ID是什么意思？
如果说是ROWID的话，楼上就正解了

[amdgaming]

就是表的主键 id啊

自动增加的

[huan_lxyd]

CREATE TABLE test11
(
id NUMBER(4),
code VARCHAR2(20)
);

INSERT INTO test11 VALUES(100, 'AA');
INSERT INTO test11 VALUES(101, 'AA');
INSERT INTO test11 VALUES(102, 'BB');
INSERT INTO test11 VALUES(103, 'BB');
INSERT INTO test11 VALUES(104, 'CC');
INSERT INTO test11 VALUES(105, 'DD');
commit;

delete from test11 where id not in(
select max(id) from(
select id,code, row_number() over(partition by code order by id desc)
from test11
) group by code
)
结果：
id code
101 AA
103 BB
104 CC
105 DD

[huan_lxyd]

delete from test11 where id not in(
select max(id) from test11 group by code
)

这样也可以。。

[kuxuan21]

[Quote=引用 8 楼 yinan9 的回复:]
SQL code
DELETE FROM T A WHERE ID < (SELECT MAX(ID) FROM T B WHERE B.CODE = A.CODE);
[/Quote]


支持！

[我心飞翔]

实测：

-- 删除重复的记录
CREATE TABLE T143
(
    ID NUMBER(4),
    NAME VARCHAR2(20)
);
INSERT INTO T143 VALUES(100, 'AA');
INSERT INTO T143 VALUES(100, 'AA');
INSERT INTO T143 VALUES(200, 'BB');
INSERT INTO T143 VALUES(200, 'BB');
INSERT INTO T143 VALUES(200, 'CC');
INSERT INTO T143 VALUES(300, 'DD');

-- 方法1(使用中间表)
-- 1.创建中间表
CREATE TABLE T144
(
    ID NUMBER(4),
    NAME VARCHAR2(20)
);
-- 2. 将数据保存到中间表
INSERT INTO T144
SELECT DISTINCT * FROM T143;
-- 3. 删除原表中所有数据
TRUNCATE TABLE T143;
-- 4. 从中间表中拷贝回数据
INSERT INTO T143 SELECT * FROM T144;
-- 5. 查看结果
SELECT * FROM T143;

[facome]

[Quote=引用 16 楼 的回复:]

delete from test11 where id not in(
select max(id) from test11 group by code
)

这样也可以。。
[/Quote]
好办法

[ssqtjffcu]

delete from t t1
 where exists (select 1
          from t t2
         where t2.code = t1.code
           and t2.id > t1.id)

[暖暖2021]

--创建测试表TABLE1
CREATE TABLE TABLE1
(
ID NUMBER NOT NULL,
CODE NUMBER NOT NULL
)
插入一些数据
ID CODE
1 1
2 2
3 2
4 3
5 5
6 5
7 5
8 4
9 3
10 6
11 2
--创建一个中间表table2 把不重复的数据放进去
create table table2 as
select * from table1 where exists( select id from(
select max(id) as id,code,count(code) as from table1
group by code
having count(code)>1
) a where table1.id=a.id)
union
select * from table1 where exists( select code from(
select code,count(code) as from table1
group by code
having count(code)=1
) a where table1.code=a.code)
--删除table1的数据，把table2的数据导入到table1
delete from table1
insert into table1 select * from table2
得到如下数据
ID CODE
1 1
7 5
8 4
9 3
10 6
11 2