MYSQL 在 'WHERE' 子句中使用 'LIKE' 在子查询中搜索

2022-01-23 00:00:00 sql sql-like where-clause subquery mysql

您将如何使用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)

相关文章