mysql - 在 1 列中选择具有多个 AND/OR/IN 条件的用户

Question

为了简单起见，我们假设该产品是一个工作板 - 使用工作板的人可以通过多个过滤器来过滤工作，例如位置、功能、技能、关键词等

我正在尝试创建一个查询，从表中选择与用户过滤条件匹配的唯一作业。

问题似乎在于将多个 OR 运算符与多个 AND 运算符组合在一起。我想也许表格设置不正确。

使用下表作为一个非常简单的示例，我想查看具有

的职位

Location = London OR Manchester
AND
Function = Marketing OR Sales
AND 
Keyword  = SEO 

Note: Location, Function and Keyword are not Groups, they are the 
filter of the user searching the jobs (maybe that is the problem?)

JobId   | Group                   | Value
1       | LocationJob             | London
1       | LocationCovered         | Leeds
1       | FunctionJob             | Tech
1       | FunctionJob2            | Engineering
1       | SkillRequired           | PHP
1       | SkillOptional           | Python
2       | LocationJob             | Newcastle
2       | LocationCovered         | Manchester
2       | FunctionJob             | SEO
2       | FunctionJob2            | PPC
2       | SkillRequired           | Marketing
3       | LocationJob             | Manchester
3       | LocationCovered         | Leeds
3       | FunctionJob             | Sales
3       | FunctionJob2            | Business Development
3       | SkillRequired           | SEO

结果应为作业 2 和 3

我目前正在执行以下操作，但我有数十万条记录，因此如果有大量连接，速度可能会非常慢..

SELECT TKeywords.UserID
FROM `Search` TKeywords
  JOIN `Search` TFunctions ON TKeywords.UserID = TFunctions.UserID AND 
       TFunctions.Value IN ('Function1', 'Function2','Function3')
  JOIN `Search` TSkills ON TKeywords.UserID = TSkills.UserID AND 
       TSkills.Value IN ('Skills1', 'Skills2','Skills3')
  JOIN `Search` TLocation ON TKeywords.UserID = TLocation.UserID AND 
       TLocation.Value IN ('Location1', 'Location2','Location3')
WHERE TKeywords.Value IN ('Keyword1', 'Keyword2', 'Keyword3')

非常感谢任何帮助。谢谢

Answer 1

这应该有效:

select s1.UserId from search s1
join (
    select UserId from search 
    where Group = 'Industry' and Value = 'Construction'
) s2 on s1.UserId = s2.UserId
where Group = 'Skill' and Value in('PHP', 'Python')