问题描述
您将如何使用LIKE"在子查询中进行搜索?
How would you use 'LIKE' to search in a subquery?
例如我试过这样做，但不起作用:
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))
它工作正常，因为它返回完全匹配，但它似乎没有返回我的其他类似记录，所以我还想检查一下:
t1.title LIKE '%t2.title%' AND t1.surname LIKE '%t2.surname%'
我该怎么做?
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?
推荐答案
使用 JOIN:
SELECT a.*
FROM mytable a
JOIN myothertable b ON a.name LIKE CONCAT('%', b.name, '%')
...但是如果在 myothertable 中对于给定的 mytable 记录有多个匹配项，则可能存在重复.
...but there could be duplicates, if there's more than one match in myothertable for a given mytable record.
使用 EXISTS:
SELECT a.*
FROM mytable a
WHERE EXISTS (SELECT NULL
FROM myothertable b
WHERE a.name LIKE CONCAT('%', b.name, '%'))
使用全文搜索MATCH (要求 myothertable 是 MyISAM)
Using Full Text Search MATCH (requires myothertable is MyISAM)
SELECT a.*
FROM mytable a
JOIN myothertable b ON MATCH(a.name) AGAINST (b.name)
这篇关于MYSQL 在 'WHERE' 子句中使用 'LIKE' 在子查询中搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！