求助一句有难度的SQL语句

[HarleyTung]

select keyword_id from db where keyword_id in (3,4)
--查询结果为
keyword_id
3
4

select keyword_id from db2
--查询结果为
keyword_id
;7;
;3;
;3;4;5;7;

--我想要的db2结果是
keyword_id
;7;
;
;5;7;

--请问这句SQL要怎么写，就是过滤db查询出来的结果加;比如查询出来的是3和4就把;3和;4过滤掉，db查询条件keyword_id in (3,4)这个可以变化，谢谢！

[huang_M]

select keyword_id 
from db2
where keyword_id not in(select keyword_id from db where keyword_id in(3,4))

[xdashewan]

我觉得你需要的是更好数据库设计而不是一句sql

[昵称90天可改一次]

REPLACE(db2.keyword_id,db.keyword_id+';','') 循环吧 db 几个就循环几次

[HarleyTung]

引用 1 楼 u012632563 的回复:

select keyword_id 
from db2
where keyword_id not in(select keyword_id from db where keyword_id in(3,4))

--我想要的db2结果是 keyword_id ;7; ; ;5;7; 这样查询出来的结果不是这个。

[HarleyTung]

引用 3 楼 chwnrthd 的回复:

REPLACE(db2.keyword_id,db.keyword_id+';','') 循环吧 db 几个就循环几次

能不能帮忙写出来下，我之前也是按照你这样的思路，可是循环怎样都查不到我想要的效果 --我想要的db2结果是 keyword_id ;7; ; ;5;7;

[HarleyTung]

引用 2 楼 xdashewan 的回复:

我觉得你需要的是更好数据库设计而不是一句sql

这个不是我设计的，我没有权利修改。

[习惯性蹭分]

一句肯定搞不定吧

create table #db(keyword_id int)
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 db2

create 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;
;

[在路上_-]

佩服

引用 9 楼 ap0405140 的回复:

create table db(keyword_id int)

insert into db
 select 3 union all
 select 4 union all
 select 5 union all
 select 7

create 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)
*/

[唐诗三百首]

create table db(keyword_id int)

insert into db
 select 3 union all
 select 4 union all
 select 5 union all
 select 7

create 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)
*/