update ##tb1 set ##tb1.dianhuahao=1 from ##tb1 a join ##tb2 b on a.nm=b.nm --left join 改为join where a.nm=b.nm and charindex(','+a.id+',', ','+b.idzh+',')>0 -----这个条件的实现办法 --否则进行别的操作 update ##tb1 set ##tb1.dianhuahao=null from ##tb1 a join ##tb2 b on a.nm=b.nm --left join 改为join where a.nm=b.nm and charindex(','+a.id+',', ','+b.idzh+',')=0 -----还有这个not in 的实现方法
两个语句可以合在一起: update ##tb1 set ##tb1.dianhuahao=case when charindex(','+a.id+',', ','+b.idzh+',')>0 then 1 else null end from ##tb1 a join ##tb2 b on a.nm=b.nm --left join 改为join where a.nm=b.nm
charindex可以,但数据一多慢死
charindex(','+a.id+',', ','+b.idzh+',')>0 报错 因为a.id 是int型,charindex函数只能用CHAR/VARCHAR、NCHAR/NVARCHAR 及BINARY/VARBINARY 改成charindex(','+rtrim(a.id)+',', ','+b.idzh+',')>0 不报错 但是更新不了 写个简单的语句验证一下也是不行的 比如 select a.nm from tb1 as a join tb2 b on a.nm=b.nm where a.nm=b.nm and charindex(','+rtrim(a.id)+',', ','+b.idzh+',')>0 这样验证条件charindex(','+rtrim(a.id)+',', ','+b.idzh+',')>0 抽出来的结果是空的阿
create table a(id int) insert into a select 1 insert into a select 2 insert into a select 3create table b (idzh varchar(50)) insert into b select '1' insert into b select '2,3' insert into b select '4,5'select * from a , b where charindex(','+ltrim(a.id)+',',','+b.idzh+',')>0id idzh 1 1 2 2,3 3 2,3怎么会空,除非你是' 2,3 '这样的
create table a(id int) insert into a select 1 insert into a select 2 insert into a select 3create table b (idzh varchar(50)) insert into b select ' 1 ' insert into b select ' 2 , 3 ' insert into b select ' 4 , 5'select * from a , b where charindex(','+ltrim(a.id)+',',','+replace(b.idzh,' ','')+',')>0 id idzh 1 1 2 2 , 3 3 2 , 3 加个replace试下
set ##tb1.dianhuahao=1
from ##tb1 a join ##tb2 b on a.nm=b.nm --left join 改为join
where a.nm=b.nm
and charindex(','+a.id+',', ','+b.idzh+',')>0 -----这个条件的实现办法
--否则进行别的操作
update ##tb1
set ##tb1.dianhuahao=null
from ##tb1 a join ##tb2 b on a.nm=b.nm --left join 改为join
where a.nm=b.nm
and charindex(','+a.id+',', ','+b.idzh+',')=0 -----还有这个not in 的实现方法
update ##tb1
set ##tb1.dianhuahao=case when charindex(','+a.id+',', ','+b.idzh+',')>0 then 1 else null end
from ##tb1 a join ##tb2 b on a.nm=b.nm --left join 改为join
where a.nm=b.nm
改成charindex(','+rtrim(a.id)+',', ','+b.idzh+',')>0 不报错 但是更新不了
写个简单的语句验证一下也是不行的
比如
select a.nm
from tb1 as a
join tb2 b on a.nm=b.nm
where a.nm=b.nm
and charindex(','+rtrim(a.id)+',', ','+b.idzh+',')>0 这样验证条件charindex(','+rtrim(a.id)+',', ','+b.idzh+',')>0 抽出来的结果是空的阿
insert into a select 1
insert into a select 2
insert into a select 3create table b (idzh varchar(50))
insert into b select '1'
insert into b select '2,3'
insert into b select '4,5'select * from a , b
where charindex(','+ltrim(a.id)+',',','+b.idzh+',')>0id idzh
1 1
2 2,3
3 2,3怎么会空,除非你是' 2,3 '这样的
insert into a select 1
insert into a select 2
insert into a select 3create table b (idzh varchar(50))
insert into b select ' 1 '
insert into b select ' 2 , 3 '
insert into b select ' 4 , 5'select * from a , b
where charindex(','+ltrim(a.id)+',',','+replace(b.idzh,' ','')+',')>0
id idzh
1 1
2 2 , 3
3 2 , 3
加个replace试下