MySQL MYSQL在'WHERE'子句中使用'LIKE'在子查询中进行搜索
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10022338/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL use 'LIKE' in 'WHERE' clause to search in subquery
提问by qwerty
How would you use 'LIKE' to search in a subquery?
您将如何使用“LIKE”在子查询中进行搜索？
E.g. i've tried doing this, but doesn't work:
例如，我试过这样做，但不起作用：
SELECT *
FROM mytable
WHERE name
LIKE '%
(SELECT name FROM myothertable)
%'
I have this so far:
到目前为止我有这个：
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))
It's working ok as it returns exact matchs, but it doesn't seem to return my other records that are similar, so I would like to also check that:
t1.title LIKE '%t2.title%' AND t1.surname LIKE '%t2.surname%'
How would i do this?
它工作正常，因为它返回完全匹配，但它似乎没有返回我的其他类似记录，所以我还想检查一下：
t1.title LIKE '%t2.title%' AND t1.surname LIKE ' %t2.surname%'
我该怎么做？
回答by OMG Ponies
Using a JOIN:
使用连接：
SELECT a.*
FROM mytable a
JOIN myothertable b ON a.name LIKE CONCAT('%', b.name, '%')
...but there could be duplicates, if there's more than one match in myothertablefor a given mytablerecord.
...但可能会有重复，如果myothertable给定mytable记录有多个匹配项。
Using EXISTS:
使用存在：
SELECT a.*
FROM mytable a
WHERE EXISTS (SELECT NULL
FROM myothertable b
WHERE a.name LIKE CONCAT('%', b.name, '%'))
Using Full Text Search MATCH(requires myothertableis MyISAM)
使用全文搜索MATCH（需要的myothertable是 MyISAM）
SELECT a.*
FROM mytable a
JOIN myothertable b ON MATCH(a.name) AGAINST (b.name)
回答by mechanical_meat
For example:
例如：
SELECT a_column
FROM mytable t
WHERE EXISTS (
SELECT 1
FROM myothertable ot
WHERE t.`name` LIKE '%' || ot.`name` || '%');
As far as terminology goes: this is known as a correlated subquery.
就术语而言：这被称为相关子查询。
回答by David
Just another way:
只是另一种方式：
select a.field, b.code
from table1 a
inner join (select code from table2 where ....) b on a.field like CONCAT('%', b.code, '%')
回答by Leo Barbas
this string works fine for me.
这个字符串对我来说很好用。
"SELECT * FROM table1 WHERE field like CONCAT('%', (SELECT id FROM table2), '%')";
回答by Dhananjay
Best way would be to create function called NameMatch()
最好的方法是创建名为 NameMatch() 的函数
Final Query :
最终查询：
SELECT * FROM mytable WHERE dbo.NameMatch(name) = 1
The function would look like :
该函数如下所示：
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
回答by Muhammad Haseeb Khan
It Worked FOR ME
它对我有用
SELECT *
FROM mytable
WHERE name
LIKE CONCAT('%',(SELECT name FROM myothertable),'%')
回答by Bellevue Esaie
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))
