Mysql - 在 "one to many"表中搜索

Question

我有一个结构为“id、user_id、skill”的表“一对多”。用户可以拥有多种技能，例如“mysql”和“php”。

当有人想搜索具有“php”和“mysql”技能的用户时，应该在搜索表单中写上“php mysql”。

我做了一些 MYSQL 查询(条件):"... WHERE skill LIKE '%php%' AND skill LIKE '%mysql%'" 但它没有返回任何内容。

我该如何改进？

我还尝试了类似“WHERE CONCAT_WS(' ', skill) LIKE '%php%' AND CONCAT_WS(' ', skill) LIKE '%mysql%'”。它也什么都不返回。

感谢您的帮助!

Answer 1

使用条件聚合:

SELECT t1.id, t1.name
FROM users t1
INNER JOIN
(
    SELECT s.user_id
    FROM skills s
    GROUP BY s.user_id
    HAVING SUM(CASE WHEN s.skill LIKE '%php%' OR
                         s.skill LIKE '%mysql%' THEN 1 ELSE 0 END) >= 2
) t2
    ON t2.user_id = t1.id

内部查询找到同时具有PHP 和MySQL 技能的用户，然后使用此结果过滤users 表以仅返回符合此要求的名称。

我在查询中假设给定技能对给定用户只会出现一次。如果同一个技能可能对同一个用户多次出现，则需要稍微修改查询。

SQLFiddle

更新:

如果您的 skills 表可能多次列出给定用户的相同技能，您可以使用 DISTINCT 忽略这些重复的条目:

SELECT t1.id, t1.name
FROM users t1
INNER JOIN
(
    SELECT s.user_id
    FROM (SELECT DISTINCT user_id, skill FROM skills) s
    GROUP BY s.user_id
    HAVING SUM(CASE WHEN s.skill LIKE '%php%' OR s.skill LIKE '%mysql%' 
               OR s.skill LIKE '%javascript%' THEN 1 ELSE 0 END) >= 3
) t2
    ON t2.user_id = t1.id

请注意，在下面的 fiddle 中，Mark 没有出现在结果集中，因为尽管他将 PHP 作为一项技能列出了两次，但他既没有 MySQL 也没有 JavaScript。

SQLFiddle