delete table a where not exists (select 1 from table group by code having a.id = max(id) )
DELETE FROM T A WHERE ID < (SELECT MAX(ID) FROM T B WHERE B.CODE = A.CODE);
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)
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 )
实测:-- 删除重复的记录 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;
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
delete from test11 where id not in( select max(id) from test11 group by code )这样也可以。。
delete from t t1 where exists (select 1 from t t2 where t2.code = t1.code and t2.id > t1.id)
--创建测试表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
http://topic.csdn.net/u/20120229/20/4784904e-8bf0-4563-8aa1-c6b9816e6757.html
select rowid from(
select rowid,
row_number() over(partition by code order by id desc) rn
from table
where rn>1))
然后把表清空,把刚才临时表中的数据再导回来。
如果说是ROWID的话,楼上就正解了
delete table a where not exists
(select 1 from table group by code having a.id = max(id)
)
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)
select id from(
select id,code row_number()over(partition by code order by id desc) rn
from table
)
where rn =1
)
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;
(
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
select max(id) from test11 group by code
)这样也可以。。
where exists (select 1
from t t2
where t2.code = t1.code
and t2.id > t1.id)
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