日期:2014-05-18  浏览次数:20514 次

单词匹配问题,a表为关键词,b表为全称词,如何匹配关键词为纯英文单词?
单词匹配问题,a表为关键词,b表为全称词,如何匹配关键词为纯英文单词?
如a表中有time sheet b表中有time sheet is a table、time sheet 123、
需要根据time sheet 把time sheet is a table、time sheet 123、都找出来。用模糊查询的话,可能会出现很多冗余数据。用精确匹配的话,有查询不出来。请各位给个解决方案吧。谢谢!

------解决方案--------------------
SQL code
select * from b where col like '%time sheet%'

------解决方案--------------------
select * from b where col like '%time%sheet% '
------解决方案--------------------
SQL code
如a表中有time   sheet   b表中有time   sheet   is   a   table、time   sheet   123、 

如果上述内容为一条记录
select b.* from b,a where charindex(a.col , b.col) > 0

------解决方案--------------------
SQL code
declare  @b table(col varchar(32))

insert @b select 'time sheet is a table'
union all select 'time sheet 123'
union all select 'time 123 sheet'

select * from @b where col like '%time sheet%'

/*
col
--------------------------------
time sheet is a table
time sheet 123

(2 row(s) affected)
*/

------解决方案--------------------
SQL code
declare  @b table(col varchar(max))

insert @b select 'time sheet is a table'
union all select 'time sheet 123'
union all select 'time 123 sheet'

declare @keyword varchar(32)
-- select @keyword=keyword from A where ... /*如果关键字从A表中查,用这句*/
set @keyword='time sheet'
while charindex('  ',@keyword)>=1
    set @keyword=replace(@keyword,'  ','')
set @keyword=replace(@keyword,' ','%')
select * from @b where col like '%'+@keyword+'%'

/*
col
------------------------------------
time sheet is a table
time sheet 123
time 123 sheet

(3 row(s) affected)
*/

------解决方案--------------------
如果a表中time sheet是一行, 并且要求能查出 time 123 sheet
那么14楼的写法就可以了.

如果不是一行
SQL code
DECLARE @a TABLE(id INT IDENTITY(1,1),v VARCHAR(20))
INSERT @a SELECT 'time' UNION SELECT 'sheet'

DECLARE @b TABLE(id INT IDENTITY(1,1),v VARCHAR(100))
INSERT @b SELECT 'time   sheet   is   a   table' 
UNION SELECT 'time   sheet   123' 
UNION SELECT 'abc time 123 sheet' 
UNION SELECT 'time'
UNION SELECT 'asbc 123 sheet xxx'

SELECT DISTINCT b.* FROM @b b
INNER JOIN @a a
    ON b.v LIKE '%' + a.v + '%' AND NOT EXISTS(SELECT 1 FROM @a WHERE b.v NOT LIKE '%' + v + '%')