预先准备
create table p(
name varchar2(10)
); insert into p values('黄伟福');
insert into p values('赵洪');
insert into p values('杨华');
每条语句执行三次插入
select rowid,p.* from p;
select distinct * from p;
查询最小rowID,只返回了一条数据
select min(rowid) from p p1 where p1.name = p1.name
查询每个重复名字的最小rowID,注意子查询返回的是条数据,并不是一条,思考一下具体执行顺序
select rowid,p1.* from p p1 where rowid = (select min(rowid) from p p2 where p1.name = p2.name);
删除重复数据,只保存数据中的最小rowID ,然后查询
delete from p p1 where rowid > (select min(rowid) from p p2 where p1.name = p2.name);
select rowid,p.* from p;
再次把数据补齐,然后查询,结果
select p1.name,p2.name from p p1,p p2 where p1.name = p2.name
全部查询,仔细看数据
select p1.rowid,p1.name,p2.rowid,p2.name from p p1,p p2 where p1.name = p2.name
还是9条数据时
select min(p1.rowid) from p p1,p p2 where p1.name = p2.name
结果