select * from 表 tem where f3=(select min(f3) from 表 where f1=tem.f1)
select * from t tem where f3=(select min(f3) from t where f3<2 and f1=tem.f1);
create table t(f1 int,f2 varchar2(10),f3 int);insert into t values(1, 'a' , 0); insert into t values(1, 'b' , 1); insert into t values(2, 'c' , 0); insert into t values(3, 'd' , 1); insert into t values(4, 'e' , 0); insert into t values(5, 'g', 2); /select * from t tem where f3=(select min(f3) from t where f3<2 and f1=tem.f1); / F1 F2 F3 ---------- ---------- ---------- 1 a 0 2 c 0 3 d 1 4 e 0已选择4行。
不用UNION也可以: select * from tbname where f3=0 or f3=1 and f1 not in ( select f1 from tbname where f3=0);
select * from tbname where f3=0 union select * from tbname a where f3=1 and not exists(select 1 from tbname b where a.f1=b.f1 and f3=0)
insert into t values(1, 'b' , 1);
insert into t values(2, 'c' , 0);
insert into t values(3, 'd' , 1);
insert into t values(4, 'e' , 0);
insert into t values(5, 'g', 2);
/select * from t tem where f3=(select min(f3) from t where f3<2 and f1=tem.f1);
/ F1 F2 F3
---------- ---------- ----------
1 a 0
2 c 0
3 d 1
4 e 0已选择4行。
select * from tbname
where f3=0 or f3=1 and f1 not in (
select f1 from tbname where f3=0);
union
select * from tbname a where f3=1 and not exists(select 1 from tbname b where a.f1=b.f1 and f3=0)