select * from tbl having count(*) >1 group by loginname,id,permission ; delete from tbl a where rowid >( select rowid from tbl x where x.loginname=a.loginname and x.id=a.id and a.permission=x.permission)
我的看法 建立一张含有loginname,I,permission的表[tbl_test] 你原来的表假设是tbl_info 用一个过程来完成create or replace procedure sp_insert_tbl_info as begin insert into tbl_test select loginname,I,permission from tbl_info group by loginname,I,permission; execute immediate 'drop table tbl_info '; execute immediate 'create tbl_info as select * from tbl_test ';end;
首先感谢大家的回复。我使用的语句是: delete from a where a.rowid!=(select max(rowid) from a b where a.loginname=b.loginname and a.permission=b.permission);虽然问题是解决了,但是我还是对这条语句有点不太明白。where子句中的a.rowid到底起着一个什么作用?为什么用这个rowid就可以将这些相同的记录给找出来?
(select min(rowid) from fee b where a.loginname=bloginname and a.id=b.id and a.permission=b.permission)
having count(*) >1 group by loginname,id,permission
;
delete from tbl a where
rowid >( select rowid from tbl x where x.loginname=a.loginname and
x.id=a.id and a.permission=x.permission)
建立一张含有loginname,I,permission的表[tbl_test]
你原来的表假设是tbl_info
用一个过程来完成create or replace procedure sp_insert_tbl_info
as
begin
insert into tbl_test select loginname,I,permission
from tbl_info
group by loginname,I,permission;
execute immediate 'drop table tbl_info ';
execute immediate 'create tbl_info as select * from tbl_test ';end;
delete from a where a.rowid!=(select max(rowid) from a b where a.loginname=b.loginname and a.permission=b.permission);虽然问题是解决了,但是我还是对这条语句有点不太明白。where子句中的a.rowid到底起着一个什么作用?为什么用这个rowid就可以将这些相同的记录给找出来?
而以上的语句,目的就是从多个重复记录中选出一个记录的ROWID,然后在删除其他rowid
select rowid from table where loginname='jim' 就可以看到不同的rowid.