Как расширить этот запрос, чтобы найти допустимые комбинации из трех элементов?

Ответ 1

В то время как решение Martin Smith не полностью решает эту проблему, оно вдохновило меня на изучение использования CTE, и я думаю, что получил его.

Как только я понял, что каждая комбинация из 3 ингредиентов - это действительно 2 разных комбинаций ингредиентов, которые разделяют 1 общий ингредиент, я решил найти все 2 комбинации ингредиентов, а затем найти любую комбинацию из тех, у которых есть по крайней мере один ингредиент, и оба эффекта что другой не имеет.

Затем убедитесь, что каждая комбинация из 3 ингредиентов не имеет ядовитых эффектов (я уже знаю, что каждая индивидуальная комбинация ингредиентов не имеет ядовитых эффектов, а только потому, что у A + B нет яда, а у B + C нет яда, это не значит A + B + C не будет иметь яда. Возможно, что расческа A с C вызовет ядовитый эффект).

Затем я присоединяю все 3 ингредиента к таблице Effect, чтобы отобразить, какие эффекты производятся с каждой комбинацией.

У этого запроса есть 3 минуты 50 секунд времени выполнения в моей системе. Это не круто. Но по крайней мере я получаю результаты, которые я хочу сейчас.

WITH Combination AS
(
    --Finds all 2 ingredient combinations that have shared effects that are not poisons
    select ROW_NUMBER() OVER (ORDER BY i1.Name, i2.Name) UniqCombination, i1.UniqIngredient UniqIngredient1, i2.UniqIngredient UniqIngredient2, COUNT(1) NumberOfEffects
    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, i1.name, i2.UniqIngredient, i2.Name
    HAVING SUM(e.poison) = 0
),
Potion AS
(
    --Matches up all 2 ingredient combinations in the Combination CTE with the effects for that combination
    SELECT DISTINCT c.UniqCombination, c.UniqIngredient1, i1.Name Ingredient1, c.UniqIngredient2, i2.Name Ingredient2, e.UniqEffect, e.Name Effect
    FROM Combination c
    INNER JOIN Ingredient i1 ON c.UniqIngredient1 = i1.UniqIngredient
    INNER JOIN Ingredient i2 ON c.UniqIngredient2 = i2.UniqIngredient
    INNER JOIN IngredientEffectJT jt1 ON c.UniqIngredient1 = jt1.UniqIngredient
    INNER JOIN IngredientEffectJT jt2 ON c.UniqIngredient2 = jt2.UniqIngredient
    INNER JOIN Effect e ON jt1.UniqEffect = e.UniqEffect AND jt2.UniqEffect = e.UniqEffect

),
BigCombination AS
(
    --Matches 2 combinations together where 1 ingredient is the same in both combinations.
    SELECT c1.UniqIngredient1, CASE WHEN c1.UniqIngredient1 = c2.UniqIngredient1 THEN c1.UniqIngredient2 ELSE c2.UniqIngredient1 END UniqIngredient2, c2.UniqIngredient2 UniqIngredient3
    FROM Combination c1
    INNER JOIN Combination c2 ON (c1.UniqIngredient1 = c2.UniqIngredient1 OR c1.UniqIngredient2 = c2.UniqIngredient1 OR c1.UniqIngredient2 = c2.UniqIngredient2) AND c1.UniqCombination < c2.UniqCombination
    --This WHERE clause sucks because there are 2 different select queries that must run twice each.
    --They have to run twice because I have to EXCEPT 1 from 2 and 2 from 1 to make sure both combinations are contributing something new.
    WHERE EXISTS( SELECT p1.UniqEffect
                  FROM Potion p1
                  WHERE p1.UniqCombination = c1.UniqCombination
                  EXCEPT
                  SELECT p2.UniqEffect
                  FROM Potion p2
                  WHERE p2.UniqCombination = c2.UniqCombination)
    AND EXISTS( SELECT p2.UniqEffect
                FROM Potion p2
                WHERE p2.UniqCombination = c2.UniqCombination
                EXCEPT
                SELECT p1.UniqEffect
                FROM Potion p1
                WHERE p1.UniqCombination = c1.UniqCombination)
),
BigPotionCombination AS
(
    --Combinations were made only from other combinations that made potions, but it possible the new
    --ingredients mixing together could create a new poison effect. This will remove combinations that create new poison effects
    SELECT DISTINCT c.*
    FROM BigCombination c
    INNER JOIN IngredientEffectJT jt1 ON c.UniqIngredient1 = jt1.UniqIngredient
    INNER JOIN IngredientEffectJT jt2 ON c.UniqIngredient2 = jt2.UniqIngredient
    INNER JOIN IngredientEffectJT jt3 ON c.UniqIngredient3 = 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)
    GROUP BY c.UniqIngredient1, c.UniqIngredient2, c.UniqIngredient3
    HAVING SUM(e.Poison) = 0
)

--Combinations have to be joined back to Effect again to display the effects that the potions have.
SELECT DISTINCT i1.Name Ingredient1, i2.Name Ingredient2, i3.Name Ingredient3, e.Name Effect
FROM BigPotionCombination c
INNER JOIN Ingredient i1 ON c.UniqIngredient1 = i1.UniqIngredient
INNER JOIN Ingredient i2 ON c.UniqIngredient2 = i2.UniqIngredient
INNER JOIN Ingredient i3 ON c.UniqIngredient3 = i3.UniqIngredient
INNER JOIN IngredientEffectJT jt1 ON c.UniqIngredient1 = jt1.UniqIngredient
INNER JOIN IngredientEffectJT jt2 ON c.UniqIngredient2 = jt2.UniqIngredient
INNER JOIN IngredientEffectJT jt3 ON c.UniqIngredient3 = 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 Ingredient1, Ingredient2, Ingredient3, Effect

Ответ 2

Попробуйте это

declare @combos table (comboId int identity, ingredient1 int, ingredient2 int, ingredient3 int null)

--create table of all unique 2 and 3 ingredient combinations (unique potions)
insert int @combos (ingredient1, ingredient2, ingredient3)
select 
    distinct
    i1.ID,
    i2.ID,
    i3.ID
from
    ingredient i1
    inner join ingredient i2 on i1.ID < i2.ID
    left outer join ingredient i3 on i2.ID < i3.ID

--create table to hold mapping between unique combinations and ingredients
declare @combo_ingredient table (ComboId int, IngredientId int)

--insert into the mapping table
insert into @combo_ingredient (ComboId, IngredientId)
select ID, ingredient1 from @combos

insert into @combo_ingredient (ComboId, IngredientId)
select ID, ingredient1 from @combos

insert into @combo_ingredient (ComboId, IngredientId)
select ID, ingredient3 from @combos where ingredient3 is not null

--create table to hold mapping between unique combinations (potions) and the effects it will have
declare @combo_effect (comboId int, effectId int)

insert into @combo_effect (comboId, effectId)
select 
    c.ComboId, ec.EffectId
from
    @combo_ingredient c
    inner join effect_ingredient ec on c.IngredientId = ec.IngredientId
having
    count(*) > 1
group by 
    c.comboId, ec.EffectId

--remove combinations that include an ingredient that do not contribute to an effect
delete from @combo_effect ce
where ce.ComboId in (
    select 
        ci.ComboId 
    from 
        @combo_ingredient ci
        inner join effect_ingredient ei on ci.IngredientId = ei.IngredientId
        left outer join @combo_effect ce on ce.ComboId = ci.ComboId and ce.EffectId = ei.EffectId
    where 
        ce.ComboId is null
)

--you can then query combo_effect for whatever information you want
--all combos with no poison effects
select comboId from 
    @combo_effect ce 
    left outer join effect e on ce.effectId = e.effectId and e.PoisonFlag = 1
group by 
    comboId
having 
    Count(e.id) = 0

Ответ 3

Не уверен, как это будет выполняться, но это намного проще, я думаю:

select
          (select Name from Ingredient where ID = e1.UniqIngredient) as Ingredient1,
          (select Name from Ingredient where ID = jt1.UniqIngredient) as Ingredient2,
          (select Name from Ingredient where ID = jt2.UniqIngredient) as Ingredient3,
          ee1.Name as Effect1,
          ee2.Name as Effect2
   from IngredientEffectJT e1
       inner join IngredientEffectJT e2 on e1.UniqEffect < e2.UniqEffect and e1.UniqIngredient = e2.UniqIngredient
       inner join IngredientEffectJT jt1 on jt1.UniqEffect = e1.UniqEffect and e1.UniqIngredient != jt1.UniqIngredient
   inner join IngredientEffectJT jt2 on jt2.UniqEffect = e2.UniqEffect and e1.UniqIngredient != jt2.UniqIngredient and jt1.UniqIngredient != jt2.UniqIngredient
       inner join Effect ee1 on e1.UniqEffect = ee1.ID
       inner join Effect ee2 on e2.UniqEffect = ee2.ID
   where ee1.Poison = ee2.Poison
;

РЕДАКТИРОВАТЬ: Забыть проверку яда.

EDIT: try2: (снова отредактирован, чтобы обрабатывать любой ингредиент как общий, а не только самый низкий ID)

select
  (select Name from Ingredient where ID = i1) as Ingredient1,
  (select Name from Ingredient where ID = i2) as Ingredient2,
  (select Name from Ingredient where ID = i3) as Ingredient3,
  min(Poison) as Poison
  -- , group_concat(Name) as Effects
  from
(
select
      a.*,
      min(e.Name) as Name,
      min(e.Poison) as Poison
   from
(
   select -- straight_join
          i1.ID as i1,
          i2.ID as i2,
          i3.ID as i3
   from IngredientEffectJT e1
       inner join IngredientEffectJT e2 on e1.UniqEffect < e2.UniqEffect and e1.UniqIngredient = e2.UniqIngredient
       inner join Effect ee1 on e1.UniqEffect = ee1.ID
       inner join Effect ee2 on e2.UniqEffect = ee2.ID and ee1.Poison = ee2.Poison
       inner join IngredientEffectJT jt1 on jt1.UniqEffect = e1.UniqEffect and e1.UniqIngredient != jt1.UniqIngredient
       inner join IngredientEffectJT jt2 on jt2.UniqEffect = e2.UniqEffect and jt1.UniqIngredient != jt2.UniqIngredient and e1.UniqIngredient != jt2.UniqIngredient
       inner join Ingredient i1
         on (i1.ID = e1.UniqIngredient  and e1.UniqIngredient < jt1.UniqIngredient and e1.UniqIngredient < jt2.UniqIngredient)
         or (i1.ID = jt1.UniqIngredient and jt1.UniqIngredient < e1.UniqIngredient and jt1.UniqIngredient < jt2.UniqIngredient)
         or (i1.ID = jt2.UniqIngredient and jt2.UniqIngredient < jt1.UniqIngredient and jt2.UniqIngredient < e1.UniqIngredient)
       inner join Ingredient i3
         on (i3.ID = e1.UniqIngredient  and e1.UniqIngredient > jt1.UniqIngredient and e1.UniqIngredient > jt2.UniqIngredient)
         or (i3.ID = jt1.UniqIngredient and jt1.UniqIngredient > e1.UniqIngredient and jt1.UniqIngredient > jt2.UniqIngredient)
         or (i3.ID = jt2.UniqIngredient and jt2.UniqIngredient > jt1.UniqIngredient and jt2.UniqIngredient > e1.UniqIngredient)
       inner join Ingredient i2 on i2.ID = e1.UniqIngredient + jt1.UniqIngredient + jt2.UniqIngredient - i1.ID - i3.ID
     group by i1.ID, i2.ID, i3.ID
) as a
inner join IngredientEffectJT as jt on a.i1 = jt.UniqIngredient or a.i2 = jt.UniqIngredient or a.i3 = jt.UniqIngredient
inner join Effect e on jt.UniqEffect = e.ID
group by i1, i2, i3, e.ID
having count(*) >= 2
) as b
group by b.i1, b.i2, b.i3
having sum(Poison) = count(*) or sum(Poison) = 0
-- order by count(distinct Name) desc
 order by i1, i2, i3
;

EDIT3:

Для SQL Server замените строку group_concat() на:

  ,(
    (
    select min(e.Name) + ',' as [data()] from IngredientEffectJT jt
       inner join Effect e on jt.UniqEffect = e.ID
       where i1=jt.UniqIngredient or i2=jt.UniqIngredient or i3=jt.UniqIngredient
       group by jt.UniqEffect
       having COUNT(*) >= 2
       for xml path('')
    )
  ) as Effects

Ответ 4

Здесь один идет на него.

;WITH IngredientCombinations AS
(
SELECT i1.UniqIngredient AS i1_UniqIngredient,
       i1.Name AS i1_Name,
       i2.UniqIngredient AS i2_UniqIngredient,
       i2.Name AS i2_Name,
       i3.UniqIngredient AS i3_UniqIngredient,
       i3.Name AS i3_Name,
       i1.UniqIngredient AS i1_UniqIngredientB,
       i2.UniqIngredient AS i2_UniqIngredientB,
       i3.UniqIngredient AS i3_UniqIngredientB       
FROM   Ingredient i1
       JOIN Ingredient i2
         ON i1.UniqIngredient < i2.UniqIngredient
       JOIN Ingredient i3
         ON i2.UniqIngredient < i3.UniqIngredient 
)
, UnpivotedIngredientCombinations AS
(
SELECT i1_UniqIngredient,
       i1_Name,
       i2_UniqIngredient,
       i2_Name,
       i3_UniqIngredient,
       i3_Name,
       UniqIngredient
FROM IngredientCombinations
UNPIVOT
   (UniqIngredient FOR idx IN 
      (i1_UniqIngredientB, i2_UniqIngredientB, i3_UniqIngredientB)
)AS unpvt),
Effects AS
(
SELECT uic.i1_Name,
       uic.i1_UniqIngredient,
       uic.i2_Name,
       uic.i2_UniqIngredient,
       uic.i3_Name,
       uic.i3_UniqIngredient,
       uic.UniqIngredient,
       e.Name,
       e.Poison,
       e.Potion,
       e.UniqEffect,
       COUNT(*) OVER (PARTITION BY i1_UniqIngredient, 
                                   i2_UniqIngredient,
                                   i3_UniqIngredient, 
                                   e.UniqEffect) AS Cnt
FROM   UnpivotedIngredientCombinations uic
       JOIN IngredientEffectJT iej
         ON iej.UniqIngredient = uic.UniqIngredient
       JOIN Effect e
         ON e.UniqEffect = iej.UniqEffect  
)
SELECT i1_Name,
       i2_Name,
       i3_Name
FROM   Effects
GROUP  BY i1_UniqIngredient,
          i2_UniqIngredient,
          i3_UniqIngredient,
          i1_Name,
          i2_Name,
          i3_Name
HAVING MAX(CASE
             WHEN Cnt = 2
                  AND Poison = 1 THEN 1
           END) IS NULL  

Ответ 5

Хорошо, так вот мой снимок.

Он основан на следующих требованиях:

• Только зелья, не допускается ядовитый эффект.
• Два или три ингредиента
• Нет дубликатов (1-2-3, 3-2-1 одинаковы)
• Все ингредиенты должны вносить свой вклад в эффект.
• Ингредиенты, которые имеют триплексный эффект, должны быть исключены, если не обеспечить другой эффект.

Я надеюсь, что имена таблиц и полей в порядке, я начал с моих собственных таблиц, но с вашими данными.

select ing1.name, ing2.name, coalesce(ing3.name, ' ') from
(
-- Gives all unique combinations of two or three ingredients
    select ing1.UniqIngredient as id1, ing2.UniqIngredient as id2, 0 as id3
    from Ingredient as ing1
    inner join Ingredient as ing2 on ing1.UniqIngredient < ing2.UniqIngredient
    UNION
    select ing1.UniqIngredient as id1, ing2.UniqIngredient as id2, ing3.UniqIngredient as id3
    from Ingredient as ing1
    inner join Ingredient as ing2 on ing1.UniqIngredient < ing2.UniqIngredient
    inner join Ingredient as ing3 on ing2.UniqIngredient < ing3.UniqIngredient
) as MainRequest
join Ingredient as ing1 on ing1.UniqIngredient = id1
join Ingredient as ing2 on ing2.UniqIngredient = id2
left outer join Ingredient as ing3 on ing3.UniqIngredient = id3
where
(   -- Check if ingredients have common positive effects that are not covered by 3 ingredients (when a 3rd ingredient is present)
    exists(
    select eff.UniqEffect, count(*)
    from /Effect eff
    join IngredientEffectJT link on link.UniqEffect = eff.UniqEffect
    where eff.potion = 1 and link.UniqIngredient in (id1, id2, id3)
    group by eff.UniqEffect
    having count(*) = 2)
    AND
    not exists(
    select eff.UniqEffect, count(*)
    from Effect eff
    join IngredientEffectJT link on link.UniqEffect = eff.UniqEffect
    where eff.potion = 1 and link.UniqIngredient in (id1, id2, id3)
    group by eff.UniqEffect
    having count(*) > 2
    )
)
-- Check if ingredients have no common negative effects
AND not exists(
    select eff.UniqEffect, count(*)
    from Effect eff
    join IngredientEffectJT link on link.UniqEffect = eff.UniqEffect
    where eff.poison = 1 and link.UniqIngredient in (id1, id2, id3)
    group by eff.UniqEffect
    having count(*) >= 2)
-- Check if every ingredient is participating (No alchemist likes a freerider)
AND exists(select link1.UniqIngredient
    from IngredientEffectJT link1
    inner join IngredientEffectJT link2 on link1.UniqEffect = link2.UniqEffect
    where link1.UniqIngredient = id1 and link2.UniqIngredient in (id2, id3))
AND exists(select link1.UniqIngredient
    from IngredientEffectJT link1
    inner join IngredientEffectJT link2 on link1.UniqEffect = link2.UniqEffect
    where link1.UniqIngredient = id2 and link2.UniqIngredient in (id1, id3))
AND (id3 = 0 or
    exists(select link1.UniqIngredient
    from IngredientEffectJT link1
    inner join IngredientEffectJT link2 on link1.UniqEffect = link2.UniqEffect
    where link1.UniqIngredient = id3 and link2.UniqIngredient in (id1, id2)))

Ответ 6

Это работает очень хорошо. http://rp.eliteskills.com/skyrim.html