Как мне расширить этот запрос, чтобы найти допустимые комбинации из трех элементов?
Я совершенно не ожидаю получить здесь какие-либо ответы, но я все равно попробую.
Итак, это произошло из-за игры в Скайрим.Мне нужен был простой способ узнать, какие ингредиенты можно комбинировать для приготовления различных зелий / ядов, поэтому я сделал таблицу ингредиентов с идентификатором и именем; таблица эффектов с идентификатором, именем, флагом яда и флагом зелья (зелье и яд являются взаимоисключающими); и таблица соединений, у которой есть ID для ингредиента и ID для эффекта.
Таким образом, каждый ингредиент имеет 4 различных эффекта, эффекты повторяются на нескольких ингредиентах. В игре вы можете комбинировать 2 или 3 ингредиента, и в результате получается зелье или яд со всеми эффектами, соответствующими как минимум 2 из используемых ингредиентов. Итак, если вы используете 3 ингредиента и эффект 1 действует как на ингредиент 1, так и на ингредиент 2, а эффект 2 действует на ингредиент 1 и ингредиент 3, результатом будет зелье / яд, имеющий как эффект 1, так и эффект 2.
Я смог самостоятельно придумать запрос, который покажет все возможные комбинации из двух ингредиентов, которые создают зелье без эффектов яда. Сначала мне нужно найти все возможные комбинации из двух ингредиентов, которые имеют только совпадающие эффекты, которые не являются «ядом»:
SELECT i1.UniqIngredient UniqIngredient1, i2.UniqIngredient UniqIngredient2
FROM Ingredient i1
CROSS JOIN Ingredient i2
INNER JOIN IngredientEffectJT jt1 ON i1.UniqIngredient = jt1.UniqIngredient
INNER JOIN IngredientEffectJT jt2 ON i2.UniqIngredient = jt2.UniqIngredient
INNER JOIN Effect e ON jt1.UniqEffect = e.UniqEffect AND jt2.UniqEffect = e.UniqEffect
WHERE i1.UniqIngredient < i2.UniqIngredient
GROUP BY i1.UniqIngredient, i2.UniqIngredient
HAVING SUM(e.Poison) = 0
Ингредиент перекрестно соединяется с ингредиентом, чтобы получить каждую комбинацию, но поскольку порядок ингредиентов не имеет значения, я бы в конечном итоге с удвоением результатов. Вот почему WHERE проверяет i1.UniqIngredient Затем я использую этот результат в виде таблицы, которую я присоединяю обратно к таблицам ингредиентов и эффектов, чтобы получить список всех возможных двух комбинаций ингредиентов, которые производят зелья, и какие эффекты каждая комбинация имеет: Используя тот же запрос, я можно найти 2 комбинации ингредиентов яда, у которых нет эффектов зелий, просто изменив строку HAVING на e.Potion вместо e.Poison. Это все хорошо, но когда я хочу ввести третий ингредиент, это становится сложным. Я в тупике. Я могу изменить этот запрос, чтобы проверить 3 ингредиента, которые имеют одинаковый эффект, но это не то, что мне нужно. Я хочу найти третий ингредиент, который имеет другое действие, чем один из ингредиентов. Любая помощь? РЕДАКТИРОВАТЬ Обновление: Итак, после нескольких часов борьбы с этим я придумал большой, уродливый, медленный, трудный для выполнения запрос (я даже не помню, почему мне пришлось сделайте это сумасшедшее условие соединения в таблице Effect. Но когда я его изменяю, весь запрос становится в 2 раза медленнее, поэтому он на самом деле быстрее, чем у меня, хотя я не знаю почему ...), что почти делает то, что я хочу. Это может быть настолько близко, насколько я могу, если у кого-то нет других идей или не видит способ улучшить мой новый запрос. Во внутреннем запросе: Это создает все возможные комбинации из 3 ингредиентов, где порядок не имеет значения и ничего не повторяется. Затем присоединяется к IngredientEffectJT и Effect ...На самом деле я не помню, для чего это сумасшедшее присоединение к Effect. Глядя на это, я подумал, что это должно было обеспечить эффект как минимум на 2 ингредиента, но это то, что делает предложение WHERE. И упрощение этого соединения Effect приводит к тому, что он работает значительно медленнее, так что ... как бы то ни было. Тогда есть GROUP BY, чтобы я мог подсчитать количество совпадающих эффектов яда. Поскольку мне пришлось сгруппировать по 3 ингредиентам, я теряю индивидуальные совпадающие эффекты, поэтому мне нужно снова объединить все эти ингредиенты с их эффектами и найти эффекты, которые совпадают. Проблема с этим запросом заключается в том, что он показывает комбинации, в которых все 3 ингредиента имеют один и тот же эффект. Эти комбинации бессмысленны, потому что вы можете сделать то же самое, используя только 2 из этих 3, так что это довольно расточительно. Итак, это лучшее, что я мог придумать. Это очень медленно, поэтому, возможно, я просто сохраню его в новой таблице, чтобы упростить и ускорить повторный запрос в будущем. SELECT i1.Name, i2.Name, e.Name
FROM (SELECT i1.UniqIngredient UniqIngredient1, i2.UniqIngredient UniqIngredient2
FROM Ingredient i1
CROSS JOIN Ingredient i2
INNER JOIN IngredientEffectJT jt1 ON i1.UniqIngredient = jt1.UniqIngredient
INNER JOIN IngredientEffectJT jt2 ON i2.UniqIngredient = jt2.UniqIngredient
INNER JOIN Effect e ON jt1.UniqEffect = e.UniqEffect AND jt2.UniqEffect = e.UniqEffect
WHERE i1.UniqIngredient < i2.UniqIngredient
GROUP BY i1.UniqIngredient, i2.UniqIngredient
HAVING SUM(e.Poison) = 0) il
INNER JOIN Ingredient i1 ON il.UniqIngredient1 = i1.UniqIngredient
INNER JOIN Ingredient i2 ON il.UniqIngredient2 = i2.UniqIngredient
INNER JOIN IngredientEffectJT jt1 ON i1.UniqIngredient = jt1.UniqIngredient
INNER JOIN IngredientEffectJT jt2 ON i2.UniqIngredient = jt2.UniqIngredient
INNER JOIN Effect e ON jt1.UniqEffect = e.UniqEffect AND jt2.UniqEffect = e.UniqEffect
ORDER BY i1.Name, i2.Name, e.Name
SELECT DISTINCT il.Name1, il.Name2, il.Name3, e.Name
FROM
(SELECT DISTINCT i1.UniqIngredient Ingredient1, i1.Name Name1, i2.UniqIngredient Ingredient2, i2.Name Name2, i3.UniqIngredient Ingredient3, i3.Name Name3
FROM Ingredient i1
INNER JOIN Ingredient i2 ON i1.UniqIngredient < i2.UniqIngredient
INNER JOIN Ingredient i3 ON i2.UniqIngredient < i3.UniqIngredient
INNER JOIN IngredientEffectJT jt1 ON i1.UniqIngredient = jt1.UniqIngredient
INNER JOIN IngredientEffectJT jt2 ON i2.UniqIngredient = jt2.UniqIngredient
INNER JOIN IngredientEffectJT jt3 ON i3.UniqIngredient = jt3.UniqIngredient
INNER JOIN Effect e ON (jt1.UniqEffect = e.UniqEffect AND (jt2.UniqEffect = e.UniqEffect OR jt3.UniqEffect = e.UniqEffect)) OR (jt2.UniqEffect = e.UniqEffect AND jt3.UniqEffect = e.UniqEffect)
WHERE (EXISTS (SELECT 1
FROM IngredientEffectJT jt1
INNER JOIN IngredientEffectJT jt2 ON jt1.UniqEffect = jt2.UniqEffect
WHERE jt1.UniqIngredient = i1.UniqIngredient
AND jt2.UniqIngredient = i2.UniqIngredient)
AND (EXISTS (SELECT 1
FROM IngredientEffectJT jt1
INNER JOIN IngredientEffectJT jt3 ON jt1.UniqEffect = jt3.UniqEffect
WHERE jt1.UniqIngredient = i1.UniqIngredient
AND jt3.UniqIngredient = i3.UniqIngredient)
OR EXISTS (SELECT 1
FROM IngredientEffectJT jt2
INNER JOIN IngredientEffectJT jt3 ON jt2.UniqEffect = jt3.UniqEffect
WHERE jt2.UniqIngredient = i2.UniqIngredient
AND jt3.UniqIngredient = i3.UniqIngredient)))
OR (EXISTS (SELECT 1
FROM IngredientEffectJT jt1
INNER JOIN IngredientEffectJT jt3 ON jt1.UniqEffect = jt3.UniqEffect
WHERE jt1.UniqIngredient = i1.UniqIngredient
AND jt3.UniqIngredient = i3.UniqIngredient)
AND EXISTS (SELECT 1
FROM IngredientEffectJT jt2
INNER JOIN IngredientEffectJT jt3 ON jt2.UniqEffect = jt3.UniqEffect
WHERE jt2.UniqIngredient = i2.UniqIngredient
AND jt3.UniqIngredient = i3.UniqIngredient))
GROUP BY i1.UniqIngredient, i1.Name, i2.UniqIngredient, i2.Name, i3.UniqIngredient, i3.Name
HAVING SUM(e.Poison) = 0) il
INNER JOIN IngredientEffectJT jt1 ON il.Ingredient1 = jt1.UniqIngredient
INNER JOIN IngredientEffectJT jt2 ON il.Ingredient2 = jt2.UniqIngredient
INNER JOIN IngredientEffectJT jt3 ON il.Ingredient3 = jt3.UniqIngredient
INNER JOIN Effect e ON (jt1.UniqEffect = e.UniqEffect AND (jt2.UniqEffect = e.UniqEffect OR jt3.UniqEffect = e.UniqEffect)) OR (jt2.UniqEffect = e.UniqEffect AND jt3.UniqEffect = e.UniqEffect)
ORDER BY il.Name1, il.Name2, il.Name3, e.Name
FROM Ingredient i1
INNER JOIN Ingredient i2 ON i1.UniqIngredient < i2.UniqIngredient
INNER JOIN Ingredient i3 ON i2.UniqIngredient < i3.UniqIngredient