问题是,查找特定组合的 CombinationId 的查询似乎非常复杂。
应该不会太糟糕。如果您想要包含所选项目的所有组合(允许使用其他项目),则类似于:
SELECT combinationID
FROM Combination
WHERE objectId IN (1, 3, 4)
GROUP BY combinationID
HAVING COUNT(*) = 3 -- The number of items in the combination
如果您只需要特定的组合(不允许额外的项目),它可能更像:
SELECT combinationID FROM (
-- ... query from above goes here, this gives us all with those 3
) AS candidates
-- This bit gives us a row for each item in the candidates, including
-- the items we know about but also any 'extras'
INNER JOIN combination ON (candidates.combinationID = combination.combinationID)
GROUP BY candidates.combinationID
HAVING COUNT(*) = 3 -- Because we joined back on ALL, ones with extras will have > 3
您还可以在此处(或在原始查询中)使用 NOT EXISTS,这似乎更容易解释。
最后,您也可以有一个简单的查询
SELECT combinationID
FROM Combination AS candidates
INNER JOIN Combination AS allItems ON
(candidates.combinationID = allItems.combinationID)
WHERE candidates.objectId IN (1, 3, 4)
GROUP BY combinationID
HAVING COUNT(*) = 9 -- The number of items in the combination, squared
换句话说,如果我们正在寻找 {1, 2},并且存在与 {1, 2, 3} 的组合,我们将得到一个 {candidates, allItems}JOIN
的结果:
{1, 1}, {1, 2}, {1, 3}, {2, 1}, {2, 2}, {2, 3}
额外的 3 结果为COUNT(*)
之后 6 行GROUP
ing,而不是 4,所以我们知道这不是我们想要的组合。