帮忙看一条sql的问题，谢谢啦！

[GT-青蛙]

一个表，信息如下:有3个字段，字段3是标识字段。

字段1	字段2	字段3
 a	  12	  0
 b	  32	  0
 b	  32	  1
 c	  53	  0
 d	  23	  0

我现在的问题是怎么去除和字段3=1 的时候字段1，字段2相同的记录，得到下面的记录,是用exists吗？方法越优越好。

字段1	字段2	字段3
 a	  12	  0
 c	  53	  0
 d	  23	  0

[bihai]

select * from tab where 字段1 +'_' + 字段2 not in (select 字段1 +'_' +字段2 from tab where 字段3='1')

[gogodiy]

CREATE TABLE t1
(
	col1 VARCHAR(1),
	col2 INT,
	col3 INT
)
INSERT INTO t1
SELECT 'a',12,0 UNION ALL
SELECT 'b',32,0 UNION ALL
SELECT 'b',32,1 UNION ALL
SELECT 'c',53,0 UNION ALL
SELECT 'd',23,0
SELECT * FROM t1

SELECT	*
FROM	t1 AS a
WHERE	NOT EXISTS (
						SELECT	*
						FROM	t1 AS b 
						WHERE	a.col1=b.col1
						AND		a.col2=b.col2
						AND		b.col3=1
				   )

------------------------------------
col1	col2	col3
a	12	0
c	53	0
d	23	0

[xb12369]

[软件钢琴师]

select * from t1 where 字段3<>1 and 字段1 not in (select 字段1 from t1 where having count(字段1)>0)

[软件钢琴师]

引用 1 楼 taiguang 的回复:

select * from t1 where 字段3<>1 and 字段1 not in (select 字段1 from t1 where having count(字段1)>0)

select * from t1 where 字段3<>1 and 字段1 not in (select 字段1 from t1 group by 字段1 having count(字段1)>0)

[开启时代]

select 字段1,字段2,字段3 from tb as a where 字段3<>1 and not exists(select 1 from tb as b where 字段3=1 and a.字段1=b.字段1 and a.字段2=b.字段2)

[GT-青蛙]

引用 3 楼 lixzhong 的回复:

select 字段1,字段2,字段3 from tb as a where 字段3<>1 and not exists(select 1 from tb as b where 字段3=1 and a.字段1=b.字段1 and a.字段2=b.字段2)

谢啦，经过实验这个是可以的