有一个表tb1:
字段如下:a,b,c,d,e,f,g,h MatchCount,Complete_match现在想:select * from tb1 as dt1 where Complete_match=0 查询出来的数据,
和select * from tb1 as dt2 where Complete_match=1查询出的数据逐条进行比较,循环比较的比较条件是:
(case when dt1.a=dt2.a then 1 else 0 end
+ case when dt1.b=dt2.b then 1 else 0 end
+ case when dt1.c=dt2.c then 1 else 0 end
+ case when dt1.d=dt2.d then 1 else 0 end
+ case when dt1.e=dt2.e then 1 else 0 end
+ case when dt1.f=dt2.f then 1 else 0 end
+ case when dt1.g=dt2.g then 1 else 0 end
+ case when dt1.h=dt2.h then 1 else 0 end
)>5 ,并把符合条件的这条数据插入到另一个表中,并把各自的MatchCount条数加1(MatchCount=MatchCount+1)更新进去。,
求答案这条存贮过程怎么写呢?
字段如下:a,b,c,d,e,f,g,h MatchCount,Complete_match现在想:select * from tb1 as dt1 where Complete_match=0 查询出来的数据,
和select * from tb1 as dt2 where Complete_match=1查询出的数据逐条进行比较,循环比较的比较条件是:
(case when dt1.a=dt2.a then 1 else 0 end
+ case when dt1.b=dt2.b then 1 else 0 end
+ case when dt1.c=dt2.c then 1 else 0 end
+ case when dt1.d=dt2.d then 1 else 0 end
+ case when dt1.e=dt2.e then 1 else 0 end
+ case when dt1.f=dt2.f then 1 else 0 end
+ case when dt1.g=dt2.g then 1 else 0 end
+ case when dt1.h=dt2.h then 1 else 0 end
)>5 ,并把符合条件的这条数据插入到另一个表中,并把各自的MatchCount条数加1(MatchCount=MatchCount+1)更新进去。,
求答案这条存贮过程怎么写呢?
符合条件的数据,是指 Complete_match=0 还是只 Complete_match=1的那条数据呢,还是2条数据都要
as
set nocount oninsert into 另一个表
select dt1.*
from (select * from tb1 where Complete_match=0)dt1,
(select * from tb1 where Complete_match=1)dt2
where (case when dt1.a=dt2.a then 1 else 0 end
+ case when dt1.b=dt2.b then 1 else 0 end
+ case when dt1.c=dt2.c then 1 else 0 end
+ case when dt1.d=dt2.d then 1 else 0 end
+ case when dt1.e=dt2.e then 1 else 0 end
+ case when dt1.f=dt2.f then 1 else 0 end
+ case when dt1.g=dt2.g then 1 else 0 end
+ case when dt1.h=dt2.h then 1 else 0 end
)>5insert into 另一个表
select dt2.*
from (select * from tb1 where Complete_match=0)dt1,
(select * from tb1 where Complete_match=1)dt2
where (case when dt1.a=dt2.a then 1 else 0 end
+ case when dt1.b=dt2.b then 1 else 0 end
+ case when dt1.c=dt2.c then 1 else 0 end
+ case when dt1.d=dt2.d then 1 else 0 end
+ case when dt1.e=dt2.e then 1 else 0 end
+ case when dt1.f=dt2.f then 1 else 0 end
+ case when dt1.g=dt2.g then 1 else 0 end
+ case when dt1.h=dt2.h then 1 else 0 end
)>5
update 另一个表 set MatchCount=MatchCount+1goexec proc_test
那就是把complete_match =0的和complete_match = 1的,共2条记录都插进去是把,然后把新插入的记录,的matchCount字段的值加1是吗?
create proc proc_test
as
set nocount on
--插入complete_match = 0的记录
insert into 新建表
select a,b,c,d,e,f,g,h,
MatchCount + 1, --直接加1,后面不用再加1更新
Complete_match
from
(
select dt1.*,
(case when dt1.a=dt2.a then 1 else 0 end
+ case when dt1.b=dt2.b then 1 else 0 end
+ case when dt1.c=dt2.c then 1 else 0 end
+ case when dt1.d=dt2.d then 1 else 0 end
+ case when dt1.e=dt2.e then 1 else 0 end
+ case when dt1.f=dt2.f then 1 else 0 end
+ case when dt1.g=dt2.g then 1 else 0 end
+ case when dt1.h=dt2.h then 1 else 0 end) as v
from
(select * from tb1 where Complete_match=0) dt1,
(select * from tb1 where Complete_match=1) dt2
)t
where v > 5
--插入complete_match = 1的记录
insert into 新建表
select a,b,c,d,e,f,g,h,
MatchCount + 1, --直接加1,后面不用再加1更新
Complete_match
from
(
select dt2.*,
(case when dt1.a=dt2.a then 1 else 0 end
+ case when dt1.b=dt2.b then 1 else 0 end
+ case when dt1.c=dt2.c then 1 else 0 end
+ case when dt1.d=dt2.d then 1 else 0 end
+ case when dt1.e=dt2.e then 1 else 0 end
+ case when dt1.f=dt2.f then 1 else 0 end
+ case when dt1.g=dt2.g then 1 else 0 end
+ case when dt1.h=dt2.h then 1 else 0 end) as v
from
(select * from tb1 where Complete_match=0) dt1,
(select * from tb1 where Complete_match=1) dt2
)t
where v > 5go