delete from tbname
where rowid not in (select min(rowid) from tb group by col1,col2..);not in的效率很慢,也可以这样:
delete from tbname
where rowid <(select max(rowid) from tb group by col1,col2..);
还可以这样:
delete from tablename a
where rowid >(select min(rowid) from table b where condition ...);
where rowid not in (select min(rowid) from tb group by col1,col2..);not in的效率很慢,也可以这样:
delete from tbname
where rowid <(select max(rowid) from tb group by col1,col2..);
还可以这样:
delete from tablename a
where rowid >(select min(rowid) from table b where condition ...);
解决方案 »
- php远程连接ORACLE时,undefined function ocilogon() 错误
- 表空间误删除,求救。
- 面试考题
- oracle/dms/instrument/ExecutionContextForJDBC 驱动出了问题?
- imp 导入前,表中已经有数据. imp有选择项,先删除表,再建立表,导入数据的选择项吗?
- 关于在windows 2003下安装ORACLE92,多个客户端连接的问题?
- 请教下如何最小化安装Oracle9i的服务器 我的硬盘空间才有1G
- 一个简单的问题,但我是新手。
- 关于存储过程中case语句的使用
- 如何改变 sqlca.sqlerrm.sqlerrmc的长度?
- 请教一条sql语句??????????急啊
- 字符串提取数字求救
(select max(rowid) from REQUEST group by id)
where a.rowid <> (select max(rowid) from tablename b where a.id=b.id);
会快一点
使用不等于怎么会比>和<快呢?
(
selec max(rowid) from table t2 where t1.id = t2.id
)
delete from table where (
select count(*) from table group by id
) > 1
delete from table where id in
(select id from table t1 where rowid !=
(select max(rowid) from table t2 where t1.id = t2.id)
)
(select max(rowid) from REQUEST group by id)这句话的意思
rowid 是表里系统自带的一个唯一字段名,group by id 按id分组以后同id的纪录的rowid不一样,从而可以根据rowid的不同把同id的记录找出来