select min from 连接

时间:2023-03-06 17:20:36

预先准备

create table p(
name varchar2(10)
); insert into p values('黄伟福');
insert into p values('赵洪');
insert into p values('杨华');

每条语句执行三次插入

select rowid,p.* from p;

select min  from 连接

select distinct * from p;

select min  from 连接

查询最小rowID,只返回了一条数据

select min(rowid) from p p1 where p1.name = p1.name

select min  from 连接

查询每个重复名字的最小rowID,注意子查询返回的是条数据,并不是一条,思考一下具体执行顺序

select rowid,p1.* from p p1 where rowid = (select min(rowid) from p p2 where p1.name = p2.name);

select min  from 连接

删除重复数据,只保存数据中的最小rowID ,然后查询

delete from p p1 where rowid > (select min(rowid) from p p2 where p1.name = p2.name);
select rowid,p.* from p;

select min  from 连接

再次把数据补齐,然后查询,结果

select  p1.name,p2.name from p p1,p p2 where p1.name = p2.name

select min  from 连接

全部查询,仔细看数据

select  p1.rowid,p1.name,p2.rowid,p2.name from p p1,p p2 where p1.name = p2.name

select min  from 连接

还是9条数据时

select  min(p1.rowid) from p p1,p p2 where p1.name = p2.name

结果

select min  from 连接