表1(tb1) id,w1,w2,w3,w4,w5,w6,w7
表2(tb2)id,w1,w2,w3,w4,w5,w6,w7 如何实现删除表2中与表1四位相同的所有数据? 例如:w1,w2,w3,w4 相同
w1,w2,w3,w5 相同
w1,w2,w3,w6 相同
...
等等所有情况4位相同数据 在线等....解决立刻给分
表2(tb2)id,w1,w2,w3,w4,w5,w6,w7 如何实现删除表2中与表1四位相同的所有数据? 例如:w1,w2,w3,w4 相同
w1,w2,w3,w5 相同
w1,w2,w3,w6 相同
...
等等所有情况4位相同数据 在线等....解决立刻给分
if object_id('tb1') is not null drop table tb1
create table tb1(id int,id1 int,id2 int,id3 int,id4 int,id5 int,id6 int,id7 int)
insert into tb1
select 1,1,2,3,4,5,6,7if object_id('tb2') is not null drop table tb2
create table tb2(id int,id1 int,id2 int,id3 int,id4 int,id5 int,id6 int,id7 int)
insert into tb2
select 1,1,2,3,4,0,0,0 union all
select 2,1,2,3,0,4,0,0 union all
select 3,0,1,2,3,4,0,0 union all
select 4,9,9,9,0,9,9,0 if object_id('fget2') is not null drop function fget2
create function fget2(@str varchar(20),@str2 varchar(20))
returns int
as
begin
declare @a int,@b varchar(20),@tmp varchar(20),@ret int
set @a=0
set @b=@str
while (len(@b)>0)
begin
set @tmp=left(@b,1)
if (charindex(@tmp,@str2)>0)
set @a=@a+1
set @b=right(@b,len(@b)-1)
end
if @a>=4
set @ret=1
else
set @ret=0
return @ret
endif object_id('fget') is not null drop function fget
create function fget(@str varchar(20))
returns table
as return
(select id from tb2 where dbo.fget2(cast(id1 as varchar(10))+
cast(id2 as varchar(10))+cast(id3 as varchar(10))+cast(id4 as varchar(10))+
cast(id5 as varchar(10))+cast(id6 as varchar(10))+cast(id7 as varchar(10)),@str)=1)declare @strid varchar(20)
declare tb1_cursor cursor for
select cast(id1 as varchar(10))+cast(id2 as varchar(10))+
cast(id3 as varchar(10))+cast(id4 as varchar(10))+
cast(id5 as varchar(10))+cast(id6 as varchar(10))+
cast(id7 as varchar(10)) strid from tb1
open tb1_cursor
fetch next from tb1_cursor
into @strid
while @@fetch_status = 0
begin
delete from tb2 where id in (select * from dbo.fget(@strid))
fetch next from tb1_cursor
into @strid
endclose tb1_cursor
deallocate tb1_cursorgo
select * from tb2
(所影响的行数为 3 行)id id1 id2 id3 id4 id5 id6 id7
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
4 9 9 9 0 9 9 0(所影响的行数为 1 行)
delete tb1
from tb1,tb2
where case when tb1.w1=tb2.w1 then 1 else 0 end+
case when tb1.w2=tb2.w2 then 1 else 0 end+
case when tb1.w3=tb2.w3 then 1 else 0 end+
case when tb1.w4=tb2.w4 then 1 else 0 end+
case when tb1.w5=tb2.w5 then 1 else 0 end+
case when tb1.w6=tb2.w6 then 1 else 0 end+
case when tb1.w7=tb2.w7 then 1 else 0 end>=4