一个表中检查将要写入的记录是否与原有记录重复共三个字段

[绿豆丫]

Field1 Field2 Field3
1 2 3
4 5 6

上面的字段值是有效记录，但现在要进行判断有如下记录，
Field1 Field2 Field3
7 1 8

像这上面的记录就不是有效记录，怎么判断这个值是不是跟原有的记录重复呢？这三个字段都需要逐个进行比较，请大家给个思路。谢谢。

[quchen520]

select * from t_a where
Field1 not in
(select Field1 from t_a
union
select Field2 from t_a
union
select Field3 from t_a
)
and
Field2 not in
(select Field1 from t_a
union
select Field2 from t_a
union
select Field3 from t_a
)
and
Field3 not in
(select Field1 from t_a
union
select Field2 from t_a
union
select Field3 from t_a
)

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2012-07-18 13:15:36
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--	Apr 22 2011 11:57:00 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([Field1] int,[Field2] int,[Field3] int)
insert [tb]
select 1,2,3 union all
select 4,5,6
--------------开始查询--------------------------
declare @Field1 int,@Field2 int ,@Field3 int 
set @Field1=7
set @Field2=1
set @Field3=8
select * from tb where checksum(@field1,@field2,@Field3) in (select checksum(field1,field2,Field3) from tb)
----------------结果----------------------------
/* Field1      Field2      Field3
----------- ----------- -----------

(0 行受影响)
*/

[SQL77]

怎么个重复法？

[科多兽]

insert into tb(Field1,Field2,Field3)
select @Field1,@Field2,@Field3
where not exists (select null from tb where
Field1 = @Field1 and Field2 =@Field2 and Field3 = @Field3)

不知道和4楼的比较起来哪个效率会高点。

[孤独加百列]

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')
BEGIN
	DROP TABLE tba
END
GO
CREATE TABLE tba
(
	Field1 INT,
	Field2 INT,
	Field3 INT
)
GO
INSERT INTO tba
SELECT 1, 2, 3 UNION
SELECT 4, 5, 6 
GO

SELECT * FROM tba
WHERE	(CHARINDEX(',' + RTRIM(7) + ',',',' + RTRIM(Field1) + ',' + RTRIM(Field2) + ',' + RTRIM(Field3) + ',') + 
		CHARINDEX(',' + RTRIM(1) + ',',',' + RTRIM(Field1) + ',' + RTRIM(Field2) + ',' + RTRIM(Field3) + ',') + 
		CHARINDEX(',' + RTRIM(8) + ',',',' + RTRIM(Field1) + ',' + RTRIM(Field2) + ',' + RTRIM(Field3) + ',')) > 0