17,086
社区成员
发帖
与我相关
我的任务
分享
delete
from taba
where (a,d,e,f) not in (
select max(a) a,d,e,f
from x
group by d, e,f)
insert into other_tb
select A, B, C, D, E, F
from old_table
where rowid in (select rd
from (select rowid rd,
row_number() over(partition by D, E, F order by A desc) rn
from old_table
where rn = 1))
--以下是删除重复数据的3种方案
--3种方案都是最优化的
--object_name, object_id. 这2个字段用来判断重复
--1.
delete
from x
where rowid in
(
select rd
from (
select rowid rd
,row_number() over(partition by object_name, object_id order by rowid) rn
from x
) x
where rn > 1
)
--2.
delete
from x
where rowid not in (
select max(rowid)
from x
group by object_name, object_id
)
--3.
create table tmp_x
AS
select x1.(字段列表..略)
from
(
select x.*, row_number() over(partition by object_name, object_id order by rowid) rn
from x
) x1
where rn = 1;
truncate table x;
insert into x
select *
from tmp_x;
drop table tmp_x;
-- 哈哈,没看清还有除去重复记录,用下面改后的SQL:
INSERT INTO TABLE_NAME
SELECT *
FROM TEST_A T1
WHERE ROWID < (SELECT MIN(ROWID)
FROM TEST_A T2
WHERE T1.D = T2.D
AND T1.E = T2.E
AND T1.F = T2.F
AND T1.A > T2.A);
[Quote=引用 4 楼 sleepzzzzz 的回复:]-- TRY IT ..
INSERT INTO TABLE_NAME
SELECT *
FROM TABLE_A T1
WHERE EXISTS (SELECT 1
FROM TABLE_A T2
WHERE T1.D = T2.D
AND T1.E = T2.E
AND T1.F = T2.F
AND T1.A > T2.A);
[Quote=引用楼主 dahuzizyd 的帖子:]