Блог

У меня возникли небольшие проблемы с SQL-запросом, и я подумал, что мне следует обратиться к мудрости толпы, чтобы увидеть, чего мне не хватает. Я почти уверен, что приведенное ниже работает, но оно кажется действительно плохим, и мне интересно, есть ли более разумный способ (в идеале с использованием объединений вместо подвыборок) для этого.

Проблема

Допустим, у меня есть несколько таблиц:

 Prize
  - PrizeId

RulePrize_Map
  - PrizeId
  - RuleId

Rule
  - RuleId

Conditional
  - ConditionalId
  - RuleId
  - InputId
  - ExpectedValue (bit)

Input
  - InputId
  

A Prize считается выигранным, когда хотя бы один Rule имеет значение true. A Rule является истинным, когда все его Conditionals значения истинны. A Conditional является «истинным», когда оно InputId либо присутствует, либо отсутствует в Input таблице, как указано в ExpectedValue поле. Возможно, это можно было бы рассматривать как эквивалентное: Count(InputId in Input table) = ExpectedValue для Conditional's InputId .

Некоторые примеры:

 Conditional (InputId = 11, ExpectedValue = 1) -> True if InputId 11 in Input Table
Conditional (InputId = 12, ExpectedValue = 0) -> True if Inputid 12 NOT in Input Table
  

Моя цель

Я хочу получить все, Prizes где хотя бы один Rule является «истинным». Я бы согласился на: «Все Rules , которые являются истинными».

Моя попытка

 select p.PrizeId from Prize p INNER JOIN RulePrize_Map rpm ON rpm.PrizeId = p.PrizeId
WHERE p.PrizeId IN
(select r.PrizeId from Rule r
where
  (select count(*) from Conditional c1 where c1.RuleId = r.RuleId)
  = 
  (select count(*) from Conditional c2
   where c2.RuleId = r.RuleId AND
   (select count(*) from Input i where i.InputId = c2.InputId) = c2.ExpectedValue
  )
)
GROUP BY p.prizeId
  

Вопрос немного изменился, поэтому я переделал ответ…

 SELECT
  PrizeId
FROM
  (
  SELECT
    PrizeRule_Map.PrizeId,
    PrizeRule_Map.RuleId
  FROM
    PrizeRule_Map
  INNER JOIN
    Rule
      ON Rule.RuleId = PrizeRule_Map.RuleId
  INNER JOIN
    Conditional
      ON Conditional.RuleId = Rule.RuleID
  LEFT JOIN
    Input
      ON Input.InputId = Conditional.InputID
  GROUP BY
    PrizeRule_Map.PrizeId,
    PrizeRule_Map.RuleId
  HAVING
    COUNT(*) = SUM(CASE Conditional.ExpectedValue
                     WHEN 1 THEN CASE WHEN Input.InputId IS NULL THEN 0 ELSE 1 END
                     WHEN 0 THEN CASE WHEN Input.InputId IS NULL THEN 1 ELSE 0 END
                   END
                   )
  )
    AS map
GROUP BY
  PrizeId
  

Чтобы получить все RuleId строки, в которых ВСЕ условные обозначения верны:

 SELECT r.RuleID
FROM Rule r
  JOIN Conditional c
    ON c.RuleId = r.RuleId
  LEFT JOIN Input i
    ON i.InputId = c.InputId
GROUP BY r.RuleID
HAVING COUNT( CASE WHEN (c.ExpectedValue=1) AND (i.InputId IS NOT NULL)
                     OR (c.ExpectedValue=0) AND (i.InputId IS NULL)
                   THEN 1
                   ELSE NULL 
              END ) 
       = COUNT( * )
  

Другой способ — возможно, более медленный, но проверить скорость не помешает. Оно не использует CASE , но разницу ( EXCEPT ) двух JOIN строк, только одна из них использует GROUP BY :

     SELECT r.RuleID
    FROM Rule r
      JOIN Conditional c
        ON c.RuleId = r.RuleId
      LEFT JOIN Input i
        ON i.InputId = c.InputId
    WHERE c.ExpectedValue = 1
    GROUP BY r.RuleID
    HAVING COUNT( i.InputId ) = COUNT( * )
  EXCEPT
    SELECT r.RuleID
    FROM Rule r
      JOIN Conditional c
        ON c.RuleId = r.RuleId
      JOIN Input i
        ON i.InputId = c.InputId
    WHERE c.ExpectedValue = 0
  

Попробуйте следующее:

 SELECT Rule.RuleId, Rule.RuleName
FROM Rule
INNER JOIN Conditional ON Rule.RuleId = Conditional.RuleId
Where Conditional.ExpectedValue == true