MYSQL使用“喜欢”在'WHERE子句中的子查询搜索

你将如何使用“喜欢”在子查询中搜索?

例如,我已经试过这样做,但不工作:

SELECT * FROM mytable WHERE name LIKE '% (SELECT name FROM myothertable) %'



我有这个至今:

SELECT * FROM t1 WHERE t1.name IN (SELECT t2.name FROM t2) AND (t1.title IN (SELECT t2.title FROM t2) OR t1.surname IN (SELECT t2.surname FROM t2))

它的工作确定,因为它返回准确的配衬,但它似乎并没有给我回类似于其他记录,所以我想还需要检查:

t1.title LIKE'%t2.title%'AND t1.surname LIKE'%t2.surname%“

我将如何做到这一点?

--------------解决方案-------------

例如:

SELECT a_column
FROM mytable t
WHERE EXISTS (
SELECT 1
FROM myothertable ot
WHERE t.`name` LIKE '%' || ot.`name` || '%');

至于术语所说:这是被称为相关子查询。

使用JOIN:

SELECT a.*
FROM mytable a
JOIN myothertable b ON a.name LIKE CONCAT('%', b.name, '%')

......但有可能是重复的,如果有一个以上的比赛myothertable对于一个给定mytable记录。

使用存在:

SELECT a.*
FROM mytable a
WHERE EXISTS (SELECT NULL
FROM myothertable b
WHERE a.name LIKE CONCAT('%', b.name, '%'))

使用全文搜索MATCH (需要myothertable是MyISAM数据)

SELECT a.*
FROM mytable a
JOIN myothertable b ON MATCH(a.name) AGAINST (b.name)

它为我工作

SELECT *
FROM mytable
WHERE name
LIKE CONCAT('%',(SELECT name FROM myothertable),'%')

最好的办法是创建函数调用NameMatch()

最后的查询:

SELECT * FROM mytable WHERE dbo.NameMatch(name) = 1

该函数将如下所示:

create function dbo.NameMatch
(@_name varchar(100))
returns bit
as begin

declare @res bit
if exists (select 1 from myothertable where @_name like '%' + name + '%' )
set @res = 1
else set @res = 0
return @res

end

SELECT * FROM mytable
WHERE name in (SELECT name FROM myothertable)

SELECT * FROM T1 WHERE t1.name IN(SELECT t2.name FROM T2)AND(t1.title IN(SELECT t2.title FROM T2)或t1.surname IN(SELECT t2.surname FROM T2))

只是另一种方式:

select a.field, b.code
from table1 a
inner join (select code from table2 where ....) b on a.field like CONCAT('%', b.code, '%')

分类:MySQL的 时间:2015-03-14 人气:0
分享到:

相关文章

Copyright (C) 55228885.com, All Rights Reserved.

55228885 版权所有 京ICP备15002868号

processed in 1.598 (s). 10 q(s)