进阶(?)AND/OR查询 [英] Advanced (?) AND / OR query

问题描述

对于一个非常简单的表结构，即.人员，条件和人员条件 (组合表)，我现在设置了一个查询，以选择具有所有选定条件的所有人员.

For quite a simple table structure, ie. Person, Criteria, and PersonCriteria (the combi-table), I have set up a query at the moment that selects all persons that possess all selected criteria.

此刻查询本身看起来像这样:

The query itself looks like this at the moment:

SELECT 
  p.PersonID   
FROM 
  Person p,     
  ( SELECT DISTINCT PersonID, CriteriaID 
    FROM PersonCriteria 
    WHERE CriteriaID in (#list_of_ids#)     
  ) k     
WHERE 
  p.PersonID= k.PersonID     
GROUP BY 
  p.PersonID     
HAVING 
  Count(*) = #Listlength of list_of_ids#

到目前为止，没有问题，一切正常.

So far no problem and everything works fine.

现在，我想为用户提供在搜索中添加一些AND和OR变量的可能性.有人会说:

Now I want to offer the possibility for the user to add some AND and OR variables in their search, ie. someone could say:

我正在寻找一个拥有以下条件的人:条件1 AND 3 AND 4 (以上查询将涵盖)AND(5或6或7)和(8或9)，依此类推...

I'm looking for a person that possesses: Criteria 1 AND 3 AND 4 (which would be covered by the query above) AND (5 OR 6 OR 7) AND (8 OR 9) and so on...

我不确定从该附加级别开始.我希望其他人也可以.. :-)

I'm not sure where to start with this additional level. I hope someone else does..:-)

推荐答案

我不得不说-我很困惑.我想不出任何解决方案，甚至会接近.我会尝试在这些方向上寻找解决方案:

I have to say - I'm stumped. I cannot think of any solution that would come even close. I would try looking for a solution in these directions:

• 用户定义的聚合函数.也许您可以创建一个函数，该函数将所需的表达式(以简化的语法)和单个人的行作为参数.然后，该函数解析表达式并将其与行进行匹配.嗯...也许MySQL包含一些串联聚合函数和regex匹配函数?那么这可能是一个解决方案(尽管可能不是很快).
• 分析功能.我不假装自己理解它们，但是据我所知，我认为它们通常都朝着这个方向发展.尽管我不知道是否会存在满足此需求的功能.

已添加: 啊，我想我明白了！虽然我认为表现会很惨.但这行得通！例如，如果您需要搜索1 AND 2 AND (3 OR 4)，则应输入:

Added: Ahh, I think I got it! Although I think the performance will be miserable. But this will work! For example, if you have the requirement to search for 1 AND 2 AND (3 OR 4) then you would write:

SELECT
    *
FROM
    Persons A
WHERE
    EXISTS (Select * from PersonCriteria B WHERE A.PersonID=B.PersonID AND CriteriaID=1)
    AND
    EXISTS (Select * from PersonCriteria B WHERE A.PersonID=B.PersonID AND CriteriaID=2)
    AND
    (
        EXISTS (Select * from PersonCriteria B WHERE A.PersonID=B.PersonID AND CriteriaID=3)
        OR
        EXISTS (Select * from PersonCriteria B WHERE A.PersonID=B.PersonID AND CriteriaID=4)
    )

添加2:这是另一个，尽管性能可能会更差:

Added 2: Here's another one, though the performance will likely be even worse:

SELECT p.* FROM Person p
    JOIN (select PersonID from PersonCriteria WHERE CriteriaID=1) c1 ON p.PersonID=c1.PersonID
    JOIN (select PersonID from PersonCriteria WHERE CriteriaID=2) c2 ON p.PersonID=c2.PersonID
    JOIN (select PersonID from PersonCriteria WHERE CriteriaID IN (3,4)) c3 ON p.PersonID=c3.PersonID

添加的3:这是2号的变体，但这实际上可能有不错的表现！

Added 3: This is a variation of No. 2, but this might actually have a chance of a decent performance!

SELECT p.* FROM
    Person p
    JOIN PersonCriteria c1 on (p.PersonID=c1.PersonID AND c1.CriteriaID=1)
    JOIN PersonCriteria c2 on (p.PersonID=c2.PersonID AND c2.CriteriaID=2)
    JOIN PersonCriteria c3 on (p.PersonID=c3.PersonID AND c3.CriteriaID IN (3,4))

如果您在列(PersonID，CriteriaID)上的PersonCriteria中添加索引(正是按此顺序！)，那么我认为它无论如何都将与您获得的索引一样快.

If you add an index to PersonCriteria on columns (PersonID,CriteriaID) (exactly in this order!), then I think it's about as fast as you're going to get in any case.

这篇关于进阶(?)AND/OR查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！