select * from tbname where f3=0 union select * from tbname where f3=1 and f1 not in (select f1 from tbname where f3=0);
select ... where f3=0 or (f3=1 and f1 not in (select f1 from where f3=0 ))
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行。
oracle 9i好像有个intersect 既不清楚了 select * from tbname where f3=0 intersect select * from tbname where f3=1;
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)
select * from tbname where f3=0 union select * from tbname where f3=1 and f1 not in (select f1 from tbname b where f3=0)
union
select * from tbname where f3=1 and f1 not in (select f1 from tbname where f3=0);
where f3=0 or (f3=1 and f1 not in (select f1 from where 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
intersect
select * from tbname where f3=1;
union
select * from tbname a where f3=1 and not exists(select 1 from tbname b where a.f1=b.f1 and f3=0)
union
select * from tbname where f3=1 and f1 not in (select f1 from tbname b where f3=0)