declare @a table
(
id int,lid int,[char] varchar(10)
)
declare @b table
(
id2 int,lid2 int,char2 varchar(20)
)
insert @a
select 1,0,'a' union
select 2,0,'b' union
select 3,0,'c' union
select 4,0,'d'
insert @b
select 9,0,'|a|bb|ab|' union
select 8,0,'|bc|da|' union
select 7,0,'|c|ea|bc|' union
select 6,0,'|d|ae|ba|' update @a
set lid=isnull((select top 1 id2 from @b where charindex('|'+t.[char]+'|',char2)>0),0)
from @a tselect * from @a--结果
/*
id lid char
----------- ----------- ----------
1 9 a
2 0 b
3 7 c
4 6 d(所影响的行数为 4 行)
*/
(
id int,lid int,[char] varchar(10)
)
declare @b table
(
id2 int,lid2 int,char2 varchar(20)
)
insert @a
select 1,0,'a' union
select 2,0,'b' union
select 3,0,'c' union
select 4,0,'d'
insert @b
select 9,0,'|a|bb|ab|' union
select 8,0,'|bc|da|' union
select 7,0,'|c|ea|bc|' union
select 6,0,'|d|ae|ba|' update @a
set lid=isnull((select top 1 id2 from @b where charindex('|'+t.[char]+'|',char2)>0),0)
from @a tselect * from @a--结果
/*
id lid char
----------- ----------- ----------
1 9 a
2 0 b
3 7 c
4 6 d(所影响的行数为 4 行)
*/
as
select id,
[lid]=isnull((select top 1 id2 from b where charindex('|'+t.[char]+'|',char2)>0),0),
[char]
from a tgo
from 表a as ta join 表b as tb
on charindex('|'+ta.char+'|', char2)<>0
(
id int,lid int,[char] varchar(10)
)
declare @b table
(
id2 int,lid2 int,char2 varchar(20)
)
insert @a
select 1,0,'a' union
select 2,0,'b' union
select 3,0,'c' union
select 4,0,'d'
insert @b
select 9,0,'|a|bb|ab|' union
select 8,0,'|bc|da|' union
select 7,0,'|c|ea|bc|' union
select 6,0,'|d|ae|ba|' select ta.id, lid=isnull(tb.id2,0),ta.char
from @a as ta left join @b as tb
on charindex('|'+ta.char+'|', char2)<>0
如果,在b表中有两行数据都符合条件怎么办呢?
下边是我的想法。
create procedure test
as
declare @chara as varchar(...)
declare @id1 as int
declare c1 cursor for select id,'|'+char+'|' from a
open c1
fetch next from c1 into @id1,@chara
while @@FETCH_STATUS = 0
if exists(select lid2 from b where char2 like '@chara%')
begin
update a set lid=(select lid2 from b where char2 like '@chara%') where a.id=@id1
--这句话有问题
end
else
continue
fetch next from c1 into @id1,@chara
end
deallocate c1