select * from GD_APPLY_INIT_INFO_READY a where rowid !=(select min(rowid)
from GD_APPLY_INIT_INFO_READY b where a.sydw_id=b.sydw_id
and to_char(a.apply_date,'yyyy')=to_char(b.apply_date,'yyyy') and a.apply_type=2)查询 15秒左右
delete
from GD_APPLY_INIT_INFO_READY a where rowid !=(select min(rowid)
from GD_APPLY_INIT_INFO_READY b where a.sydw_id=b.sydw_id
and to_char(a.apply_date,'yyyy')=to_char(b.apply_date,'yyyy') and a.apply_type=2)
删除一直在转,执行不了!
from GD_APPLY_INIT_INFO_READY b where a.sydw_id=b.sydw_id
and to_char(a.apply_date,'yyyy')=to_char(b.apply_date,'yyyy') and a.apply_type=2)查询 15秒左右
delete
from GD_APPLY_INIT_INFO_READY a where rowid !=(select min(rowid)
from GD_APPLY_INIT_INFO_READY b where a.sydw_id=b.sydw_id
and to_char(a.apply_date,'yyyy')=to_char(b.apply_date,'yyyy') and a.apply_type=2)
删除一直在转,执行不了!
from GD_APPLY_INIT_INFO_READY b where a.sydw_id=b.sydw_id
and to_char(a.apply_date,'yyyy')=to_char(b.apply_date,'yyyy') and a.apply_type=2)放到 sqlplus中执行要多久,别用PLSQL工具执行。
select *
from (select a.*,
row_number() over(partition by a.sydw_id, to_char(a.apply_date, 'yyyy') order by rownum) rn
from GD_APPLY_INIT_INFO_READY a
where a.apply_type = 2)
where rn = 1;
drop table GD_APPLY_INIT_INFO_READY;
alter table temp rename to GD_APPLY_INIT_INFO_READY;
select * from GD_APPLY_INIT_INFO_READY a where rowid !=(select min(rowid)
from GD_APPLY_INIT_INFO_READY b where a.sydw_id=b.sydw_id
and to_char(a.apply_date,'yyyy')=to_char(b.apply_date,'yyyy') and a.apply_type=2)为啥是a.apply_type=2
这里你是要不是应该写到外面,还是写b.
where rowid not in (select min(rowid)
from GD_APPLY_INIT_INFO_READY b
where a.sydw_id = b.sydw_id
and to_char(a.apply_date, 'yyyy') =
to_char(b.apply_date, 'yyyy'))
and a.apply_type = 2