我有一个如下表:
user | item
-------------
X | Apple
X | Orange
X | Pear
Y | Orange
Y | Pear
Z | Apple
Z | Orange
我的目标是有 3 个搜索选项:ANY
, ALL
(至少),EXACT
Where
ANY返回至少搜索过一项的用户列表,因此搜索“Apple” - ANY 将返回 X,Z,搜索“Apple, Orange”- ANY 将返回 X,Y,Z
ALL返回搜索了所有项目的用户列表,因此搜索“Apple” - ALL 将返回 X,Z,搜索“Apple, Orange”- ALL 将返回 X,Z
EXACT返回搜索了所有项目但没有搜索其他项目的用户列表,因此搜索“Apple”- EXACT 将不会返回任何结果,搜索“Apple,Orange”- EXACT 将返回 Z
我已经解决了前两个问题:
ANY
SELECT user FROM users_items WHERE item IN ($item_array);
ALL
SELECT user FROM users_items WHERE item IN ($item_array)
GROUP BY user HAVING COUNT(DISTINCT item) = $item_search_count;
但我不知道如何进行精确搜索。我能想到的最好的办法是:
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;
这起初不起作用,因为在真实的数据集中,用户或项目可能为空,但当我将其更新为:
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;
这可行,但查询速度确实很慢。
有没有更好的方法来查询“所有匹配集合的项目,排除不匹配集合的项目”?