如关键字 'aaa,bbb,ccc,ddd '
表#table int @id , varchar @keyword
1 aaa,bbb,ccc,ddd
2 aaa,ccc
3 aaa
4 aaa,bbb,ccc
输入关键字后匹配结果顺序
1 aaa,bbb,ccc,ddd
4 aaa,bbb,ccc
2 aaa,ccc
3 aaa
------解决方案--------------------
create table test(keyword varchar(100))
insert test values( 'aaa,bbb,ccc,ddd ')
insert test values( 'aaa,ccc ')
insert test values( 'aaa ')
insert test values( 'aaa,bbb,ccc ')
declare @testdb varchar(500)
set @testdb= 'aaa,bbb,ccc,ddd '
create table #tmp(a varchar(100))
declare @aaa varchar(8000)
set @aaa= 'insert #tmp select * from(select ' ' '+replace(@testdb, ', ', ' ' 'as a union all select ' ' ')+ ' ' ') as a '
-- [email protected]
exec(@aaa)
select * from test where exists (select 1 from #tmp where ', '+test.keyword+ ', ' like '%, '+a+ ',% ')
order by len(keyword) desc
drop table test,#tmp
--结果
/*
keyword
-----------
aaa,bbb,ccc,ddd
aaa,bbb,ccc
aaa,ccc
aaa
*/
------解决方案--------------------
create table a(aname varchar(100))
declare @str varchar(100),@T_sql varchar(8000)
set @str = 'aaaa,bbbb,cccc,dddd '
set @T_sql= 'insert a select ' ' '
set @[email protected]_sql+replace(@str, ', ', ' ' ' union all select ' ' ')+ ' ' ' '
--select @T_sql
exec(@T_sql)
--select * from a
declare @t table(id int identity(1,1),keyword varchar(100))
insert @t select 'aaaa,bbbb,cccc,dddd '
union all select 'aaaa,bbbb '
union all select 'aaaa '
union all select 'aaaa,bbbb,cccc '
union all select 'eeee,ffff '
--select * from @t
--select keyword,a.* from a right join @t b on charindex( ', '+a.aname+ ', ', ', '+b.keyword+ ', ')> 0
select keyword from a inner join @t b on charindex( ', '+a.aname+ ', ', ', '+b.keyword+ ', ')> 0 group by keyword order by count(*) desc
drop table a
昨天为了你的问题我都加了半个小时班了，当时考虑得有点多了，想把查询条件关联表中不存在的数据也检索出来，比如a,b,c,d；后来在回家的路上才想起是白考虑了！哎，浪费了半个小时