大概这样:
delete from employee where rowid not in (
select max(t1.rowid) from employee t1 group by t1.emp_id,t1.emp_name,t1.salary);-
delete from employee where rowid not in (
select max(t1.rowid) from employee t1 group by t1.emp_id,t1.emp_name,t1.salary);-
解决方案 »
- 求助,关于sqlldr往数据库里导入csv文件的一些错误
- 如何避免oracle集群重启服务器
- 寻求一本好的ORACLE书
- powerdesignerpdm模型产生数据库的时候,有一部分外建创建不了
- oracle备份文件的路径中不能含有中文字符的问题
- 创建logmnr出现的问题。。。
- 请问这样一个要求的SQL语句怎么写?
- ^_^^_^关于存储过程和job的问题,高手帮忙看看^_^^_^^_^^_^(分不多了!)
- 如何对数据库中的表进行截断?请高手指点,非常急,在线等!!!!
- 请各位高手帮忙,怎么写这个语句?
- 在ORACLE中取最新的数据的前几条,怎么取?象TOP 10用法一样
- 急!!!请问如何在脚本中获取数据库中某张表的字段类型!!!
create T_bak as select distinct A,B,C from T
但是,问题是创建的临时表还有id列没法对应地插入进去
请高手们提点一下,急啊
ljfdd(夜风之舞):
可是ID不属于distinct,distinct用了之后结果不是与原来的是一样的么
id A B C
1 2 2 3
2 2 2 3
3 3 4 5
4 5 7 8
5 3 4 5
看看这个:
Oracle中大批量删除数据的方法
http://www.eygle.com/archives/2005/04/oracleoeouaeeae.html条件就按2楼的即可。
Insert Into A_tmp Values (2,2,3);
Insert Into A_tmp Values (3,4,5);
Insert Into A_tmp Values (5,7,8);
Insert Into A_tmp Values (3,4,5);
Commit;Select rownum Id ,A,B,C From
(select distinct A,B,C from a_Tmp)结果
1 2 2 3
2 3 4 5
3 5 7 8
(select distinct A,B,C from A_tmp)
delete from t
where rowid in
(select min(rowid) from t group by a, b, c having count(*) > 1);
重复几次就好了!或要着写成程序块
declare
begin
loop
delete from t
where rowid in
(select min(rowid) from t group by a, b, c having count(*) > 1);
commit;
if sql%rowcount = 0 then
exit;
end if;
end loop;
end;
(
select id from
(
select A,B,C,max(id) from tableName group by A,B,C
)
)
delete from tableName where not exists
(
select id from
(
select max(id) id from tableName group by A,B,C
)
where id = tableName.id
)