delete from tbname a,(select min(rowid) no from tbname group by id) b where a.rowid <> b.no;
delete from tbname a,(select min(rowid) no from tbname group by id) b where a.rowid <> b.no;我执行这条语句,报错啊?
改一下吧delete tbname where rowid not in(select min(rowid) from tbname group by id);
wylwyl1130(落雪山林)的语句非常高效~~
to:ORARichard(没钱的日子好难过啊)delete tbname where rowid not in(select min(rowid) from tbname group by id)好像要把id改为dm才行吧
drop table tableA; create table tableA ( id varchar2(4), dm varchar2(20) ); insert into tableA values('0101', '上海'); insert into tableA values('0102', '北京'); insert into tableA values('0103', '上海'); insert into tableA values('0104', '北京'); insert into tableA values('0105', '广州'); insert into tableA values('0106', '北京'); commit; --所有记录 select * from tableA order by id; --要删除的记录 select * from tableA where id not in (select min(id) from tableA group by dm); --删除记录 delete from tableA where id not in (select min(id) from tableA group by dm);
where a.rowid <> b.no;
where a.rowid <> b.no;我执行这条语句,报错啊?
create table tableA
(
id varchar2(4),
dm varchar2(20)
);
insert into tableA values('0101', '上海');
insert into tableA values('0102', '北京');
insert into tableA values('0103', '上海');
insert into tableA values('0104', '北京');
insert into tableA values('0105', '广州');
insert into tableA values('0106', '北京');
commit;
--所有记录
select * from tableA order by id;
--要删除的记录
select * from tableA where id not in (select min(id) from tableA group by dm);
--删除记录
delete from tableA where id not in (select min(id) from tableA group by dm);