select keyword_id
from db2
where keyword_id not in(select keyword_id from db where keyword_id in(3,4))
from db2
where keyword_id not in(select keyword_id from db where keyword_id in(3,4))
循环吧 db 几个就循环几次
--我想要的db2结果是
keyword_id
;7;
;
;5;7;这样查询出来的结果不是这个。
能不能帮忙写出来下,我之前也是按照你这样的思路,可是循环怎样都查不到我想要的效果--我想要的db2结果是
keyword_id
;7;
;
;5;7;
insert into #db
select 3 union all
select 4
create table #db2(keyword_id varchar(50))
insert into #db2
select ';7;' union all
select ';3;' union all
select ';3;4;5;7'
declare @keyword varchar
declare cur1 cursor for select keyword_id from #db
where keyword_id in(3,4)
open cur1
fetch next from cur1 into @keyword
while @@fetch_status=0
begin
update #db2 set keyword_id =replace(keyword_id,';'+@keyword,'')
where charindex(';'+@keyword,keyword_id)>0
fetch next from cur1 into @keyword
end
close cur1
deallocate cur1
select * from #db2
if OBJECT_ID('db1')>0 drop table db1
if OBJECT_ID('db2')>0 drop table db2create table db1(keyword_id varchar(10))
insert into db1
select '3' union
select '4'create table db2(keyword_id varchar(500))
insert into db2
select ';7;' union
select ';3;' union
select ';3;4;5;7;';with t1 as
(
select db1.keyword_id+';' kd,
ROW_NUMBER() over(order by db1.keyword_id) as n
from db1
)
,cte as
(
select REPLACE(db2.keyword_id, t1.kd, '') as keyword_id, t1.n
from db2 join t1 on t1.n=1
union all
select REPLACE(cte.keyword_id, t1.kd, ''), t1.n
from cte join t1 on t1.n=cte.n+1
)
select keyword_id from cte
where n=(select MAX(n) from cte)
drop table db1
drop table db2
-----------结果--------
;7;
;5;7;
;
create table db(keyword_id int)insert into db
select 3 union all
select 4 union all
select 5 union all
select 7create table db2(keyword_id varchar(50))insert into db2
select ';7;' union all
select ';3;' union all
select ';3;4;5;7;'
-- test1
declare @tsql varchar(6000)select @tsql='replace('+isnull(@tsql,'keyword_id')+','''+rtrim(keyword_id)+';'','''')'
from db where keyword_id in (3,4)select @tsql='select '+@tsql+' ''keyword_id'' from db2'exec(@tsql)/*
keyword_id
-------------------------
;7;
;
;5;7;(3 row(s) affected)
*/
-- test2
declare @tsql varchar(6000)select @tsql='replace('+isnull(@tsql,'keyword_id')+','''+rtrim(keyword_id)+';'','''')'
from db where keyword_id in (5,7)select @tsql='select '+@tsql+' ''keyword_id'' from db2'exec(@tsql)/*
keyword_id
-------------------------
;
;3;
;3;4;(3 row(s) affected)
*/