所有至少有一个子节点拥有全部子节点的行都符合条件。

Question

我遇到了一个SQL查询的小问题，想要请教大家的智慧来看看我错过了什么。我相当确定以下代码可以运行，但它似乎非常低效，我想知道是否有更聪明的方法（最好使用连接而不是子查询）来完成这个任务。 问题 假设我有一些表格：

Prize
  - PrizeId

RulePrize_Map
  - PrizeId
  - RuleId

Rule
  - RuleId

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

Input
  - InputId

当至少有一个规则为真时，将赢得奖项。当所有条件都为真时，规则才为真。当其输入ID存在于输入表中或未出现在输入表中（由期望值字段指定）时，条件为“真”。这可能等价于:“在输入表中计数（输入ID）=期望值”对于条件的输入ID。

一些例子:

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

我的目标

我想获取所有至少有一个“规则”为“true”的“奖品”。如果只能得到“所有真实的规则”，我也会满足。

我的尝试

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

- Matt Mitchell

---

只是一个想法：也许可以考虑排除所有规则都为假的奖项... - pascal

Answer 1

问题有所变化，因此我重新回答了这个问题...

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

- MatBailie

---

这里使用的是“任何规则为真”的方式。如果你想要“所有规则都为真”，那么从主查询的SELECT和GROUP BY中删除“PrizeRule_Map.RuleId”行（然后你也不需要包装查询了）。 - MatBailie

非常感谢您根据问题更改更新了“奖励”。 - Matt Mitchell

@Dems：LEFT JOIN ON Input.InputId = ...，你漏掉了 Input。 - ypercubeᵀᴹ

@ypercube：不，那是故意的，我想看看 OP 是否会注意到。诚实地说。 脸红 - MatBailie

@Dems - 哈哈，我确实这样做了，但是不想在一个完美的答案上吹毛求疵。 - Matt Mitchell

Answer 2

获取所有条件都为真的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，而是使用两个 JOIN 的差异 (EXCEPT)，其中只有一个使用 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

- ypercubeᵀᴹ

---

你使用了 COUNT(NULL|1)，而我使用了 SUM(0|1)，你知道这两者在性能上有什么区别吗？ - MatBailie

在布尔逻辑中需要指定 i.InputId 或 c.InputId 吗？ - MatBailie

相当聪明的第二个选项。我也喜欢 Count 方法的清晰度。 - Matt Mitchell

Answer 3

请尝试以下操作：

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

- Hasan Fahim

---

你的查询没有考虑与规则关联的“全部”条件，并且甚至没有查看 Input 的内容。 - pascal

这将显示所有只有一个条件为真的规则。它也不能正确地评估条件（ExpectedValue == true并不一定意味着条件为真，而是表示如果输入表包含条件的InputId，则条件为真）。 - Matt Mitchell

这个无法处理 expectedValue = false 且输入表中存在值的情况。 - Jon Egerton

@pascal、@Graphain和@Jon Egerton。问题已经修改。目标不同了。我的答案是针对之前的目标，即“我想获取所有规则，其中它们所有条件都为“true”。”。 - Hasan Fahim

阅读原始问题，条件“真”被定义为“输入计数=条件中的期望值”。 - Matt Mitchell