select a.*,
case when exists(select 1 from planadmin where charindex('、'+a.f27+'、','、'+repkace(retwo,',','、')+'、') > 0) then 1 else 0 end as keyid
from add_final as a
case when exists(select 1 from planadmin where charindex('、'+a.f27+'、','、'+repkace(retwo,',','、')+'、') > 0) then 1 else 0 end as keyid
from add_final as a
亮马台供水工程 D130801,D130803,D130804,D130805
的项目号之间用的是逗号而不是 、 号.
create table add_final(F6 nvarchar(20),F7 int,F27 varchar(10))
insert into add_final select '方里镇野狐咀村',174,'D130503'
insert into add_final select '方里镇陶家庄村',295,'D130501'
insert into add_final select '卜家乡城前头村',250,'D130802'
insert into add_final select '卜家乡北桥村',215,'D130809'
insert into add_final select '卜家乡营房坡村',340,'D130802'
insert into add_final select '卜家乡姚郑村',444,'D130804'create table planadmin(pro_name nvarchar(20),retwo varchar(50))
insert into planadmin select'华子山供水工程','D130502、D130504、D130501、D130524'
insert into planadmin select'陶家庄供水工程','D130501'
insert into planadmin select'方里镇野狐咀供水工程','D130501'
insert into planadmin select'方里镇华子山供水工程','D130501'
insert into planadmin select'曹家庄村供水工程','D130501'
insert into planadmin select'东奉集中供水工程','D130810、D130808、D130809'
insert into planadmin select'亮马台供水工程','D130801,D130803,D130804,D130805'
go
select *,
case when exists(select 1 from planadmin where charindex('、'+a.F27+'、','、'+replace(retwo,',','、')+'、')>0) then 1 else 0 end as keyid
from add_final a
go
drop table planadmin,add_final
/*
F6 F7 F27 keyid
-------------------- ----------- ---------- -----------
方里镇野狐咀村 174 D130503 0
方里镇陶家庄村 295 D130501 1
卜家乡城前头村 250 D130802 0
卜家乡北桥村 215 D130809 1
卜家乡营房坡村 340 D130802 0
卜家乡姚郑村 444 D130804 1(6 行受影响)
*/