--类似这样,在子查询里的where写出表的主键就可以,如果没有主键就要写出全部列 delete test a where a.rowid!=(select max(rowid) from test b where a.id=b.id and a.num=b.num);
craete table t_bak as select id,name from ( select id,name,row_number() over(partition by id order by id) rn from t ) where rn = 1 drop table t;
rename t_bak to t;
create table tb1 as select 1 as a from dual union all select 1 as a from dual union all select 1 as a from dual union all select 1 as a from dual union all select 2 as a from dual union all select 2 as a from dual union all select 2 as a from dual ; delete from tb1 where not exists (select 1 from (select a, max(rowid) rd from tb1 group by a) t where t.rd = tb1.rowid and t.a = tb1.a) 提供参考
select distinct * from stock 10856行数据 全部的select count(*) from stock 是21688行 我没怎么看懂你的语句 我连接现场的数据库的
create table stock2 as select * from stock where 1=2; insert into stock2 select distinct * from stock; truncate table stock; insert into stock select * from stock2; commit; drop table stock2;
delete from stock a where not exists(select 1 from stock b where a.warehousecode=b.warehousecode and a.locationcode=b.locationcode and a.materialcode=b.materialcode and a.stockqty=b.stockqty and a.prepareqty=b.prepareqty and a.status=b.status and a.indate=b.indate and a.receivingcode=b.receivingcode and a.qadwarehouse=b.qadwarehouse and a.rowid<b.rowid)这样写 好像不对啊
delete test a
where a.rowid!=(select max(rowid) from test b where a.id=b.id and a.num=b.num);
select id,name from (
select id,name,row_number() over(partition by id order by id) rn from t
) where rn = 1
drop table t;
rename t_bak to t;
create table tb1 as
select 1 as a from dual union all
select 1 as a from dual union all
select 1 as a from dual union all
select 1 as a from dual union all
select 2 as a from dual union all
select 2 as a from dual union all
select 2 as a from dual
;
delete from tb1
where not exists (select 1
from (select a, max(rowid) rd from tb1 group by a) t
where t.rd = tb1.rowid
and t.a = tb1.a)
提供参考
select distinct * from stock 10856行数据
全部的select count(*) from stock 是21688行
我没怎么看懂你的语句
我连接现场的数据库的
insert into stock2 select distinct * from stock;
truncate table stock;
insert into stock select * from stock2;
commit;
drop table stock2;
where not exists(select 1 from stock b where a.warehousecode=b.warehousecode and
a.locationcode=b.locationcode and a.materialcode=b.materialcode and a.stockqty=b.stockqty
and a.prepareqty=b.prepareqty and a.status=b.status and a.indate=b.indate
and a.receivingcode=b.receivingcode and a.qadwarehouse=b.qadwarehouse
and a.rowid<b.rowid)这样写 好像不对啊