求一sql语句

[liuyude]

表中有三个字段t(f1,f2,f3)
f1 f2 f3
1 a 0
1 b 1
2 c 0
3 d 1
4 e 0
5 g 2
表的内容象上面这样。
现在我要得到：
f3=0的所有行
再加上f3=1并且f1不在f3=0中的所有行。
(换句话说，要是f3=0的记录中出现过的f1，不需要再出现)
结果就是：
1 a 0
2 c 0
3 d 1
4 e 0

[bzszp]

select * from tbname where f3=0
union
select * from tbname where f3=1 and f1 not in (select f1 from tbname where f3=0);

[pengdali]

select * from 表 tem where f3=(select min(f3) from 表 where f1=tem.f1)

[pengdali]

select * from t tem where f3=(select min(f3) from t where f3<2 and f1=tem.f1);

[pengdali]

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行。

[dragonl3]

不用UNION也可以：
select * from tbname
where f3=0 or f3=1 and f1 not in (
select f1 from tbname where f3=0);

[beckhambobo]

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)