当前位置: 代码迷 >> Sql Server >> 一个表中稽查将要写入的记录是否与原有记录重复共三个字段
  详细解决方案

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

热度:32   发布时间:2016-04-27 11:51:40.0
一个表中检查将要写入的记录是否与原有记录重复共三个字段
Field1 Field2 Field3
1 2 3
4 5 6

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

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

------解决方案--------------------
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
)
------解决方案--------------------
SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN    DROP TABLE tbaENDGOCREATE TABLE tba(    Field1 INT,    Field2 INT,    Field3 INT)GOINSERT INTO tbaSELECT 1, 2, 3 UNIONSELECT 4, 5, 6 GOSELECT * FROM tbaWHERE    (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
------解决方案--------------------
SQL code
------------------------------ 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 allselect 4,5,6--------------开始查询--------------------------declare @Field1 int,@Field2 int ,@Field3 int set @Field1=7set @Field2=1set @Field3=8select * from tb where checksum(@field1,@field2,@Field3) in (select checksum(field1,field2,Field3) from tb)----------------结果----------------------------/* Field1      Field2      Field3----------- ----------- -----------(0 行受影响)*/
------解决方案--------------------
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楼的比较起来哪个效率会高点。
  相关解决方案