如何将查询结果限制为精确的组匹配 [英] How to limit query results to exact group match

问题描述

我有一张如下所示的表格:

I have a table like the following:

user  | item
-------------
   X  | Apple
   X  | Orange
   X  | Pear
   Y  | Orange
   Y  | Pear
   Z  | Apple
   Z  | Orange

我的目标是有 3 个搜索选项:ANY、ALL(至少)、EXACT

My goal is to have 3 search options: ANY, ALL (At Least), EXACT

哪里

• ANY 返回至少搜索过一项的用户列表，因此搜索Apple" - ANY 将返回 X,Z，搜索Apple, Orange" -ANY 会返回 X,Y,Z

• ANY returns a list of users who have at least one item searched for, so searching for "Apple" - ANY would return X,Z, searching for "Apple, Orange" - ANY would return X,Y,Z

ALL 返回搜索过所有项目的用户列表，因此搜索Apple" - ALL 将返回 X,Z，搜索Apple, Orange" - ALL 将返回返回 X,Z

ALL returns a list of users who have all items searched for, so searching for "Apple" - ALL would return X,Z, searching for "Apple, Orange" - ALL would return X,Z

EXACT 返回搜索了所有项目而没有其他项目的用户列表，因此搜索Apple" - EXACT 不会返回任何结果，搜索Apple, Orange"" - EXACT 将返回 Z

EXACT returns a list of users who have all items searched for and no other items, , so searching for "Apple" - EXACT would return no results, searching for "Apple, Orange" - EXACT would return Z

我已经解决了前两个:

ANY

SELECT user FROM users_items WHERE item IN ($item_array);

全部

SELECT user FROM users_items WHERE item IN ($item_array) 
GROUP BY user HAVING COUNT(DISTINCT item) = $item_search_count;

但我不知道如何进行精确搜索.我能想到的最好的方法是:

But I can't figure out how to do the exact search. The best I can come up with is:

SELECT user FROM users_items WHERE item IN ($item_array) AND
user NOT IN (
     SELECT user FROM users_items WHERE item NOT IN ($item_array)
)
GROUP BY user HAVING COUNT(DISTINCT item) = $item_search_count;

这起初不起作用，因为在真实数据集中，用户或项目都可能为空，但是当我将其更新为:

This didn't work at first because in the real data set, either user or item could be null but when I updated it to:

SELECT user FROM users_items WHERE item IN ($item_array) AND
user NOT IN (
     SELECT user FROM users_items WHERE item NOT IN ($item_array) 
     AND user IS NOT NULL AND item IS NOT NULL
)
GROUP BY user HAVING COUNT(DISTINCT item) = $item_search_count;

这行得通，但查询真的很慢.

This worked, but the query is really slow.

是否有更好的方法来查询所有与集合匹配的项目，排除与集合不匹配的项目"?

Is there a better way to query for "all items that match set, exclude items that don't match set"?

推荐答案

以下有效，应该可以解决您的尺寸问题.但它可能会很慢，请使用您的真实数据尝试一下.

The following works, and should solve your size problem. But it could be slow, just try it with your real data.

Select 
    User 
FROM 
    users_items 
WHERE 
    User NOT IN
    (
        SELECT
            users.User
        FROM 
            users_items AS users
        JOIN 
            users_items AS users2 ON users.User = users2.User
        WHERE
            users2.Item NOT IN ('Apple','Orange')
        GROUP BY 
            users.User
    )
GROUP BY 
    User

这篇关于如何将查询结果限制为精确的组匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！