今天去面试!我给大意了,直接写了个删除语句!又中招了。。。
在网上找了半天也没找到合适的代码!
回来想了想才知道自已太大意了!想了良久,想出了这么个办法,
insert into fuck values(user_id.nextval,'liang','123');
select * from fuck
delete from fuck
where id in (select id
from (select t.*, rownum r from fuck t where name = 'liang')
where r > 1);
今天又想了想,还是不行呀!只能指定值!一不做二不休,又是几个小时的研究,他奶奶的,功夫不负有心上,终于思考出一个思路,并成功!也许有更简单的写法,
如果对你有帮助,别忘给我留个个脚印哈,代码如下
declare
v_sql varchar2(500);
v_tempName varchar2(30);
cursor t_name is/*提取有重得的名字*/
select name from fuck group by name having count(name) >1;
begin
v_sql:='delete from fuck
where id in (select id
from (select t.*, rownum r from fuck t where name = :1)
where r > 1) ';
for t_names in t_name loop /*循环读取并删除*/
execute immediate v_sql using t_names.name;
dbms_output.put_line('成功删除重复数据: '||t_names.name);
end loop;
end;