求大神帮我看看这条语句

lxs512297816 2013-10-23 01:53:36

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)
删除一直在转，执行不了！

...全文

339 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

li_shiye 2013-10-24

打赏
举报

delete from GD_APPLY_INIT_INFO_READY a 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

俊凯 2013-10-24

打赏
举报

用rowid去删

一十七 2013-10-23

打赏
举报


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.

zmn0079 2013-10-23

打赏
举报

还有索引呢？解释计划呢？

u010412956 2013-10-23

打赏
举报

或者下面这样做

create table temp as 
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;

u010412956 2013-10-23