sql - 选择具有多个满足某些约束的子关系的所有行

假设我有一个篮子清单，里面可以装一定重量的水果：

Table baskets
(id, name)
----------------
1, 'apples, oranges and more'
2, 'apples and small oranges'
3, 'apples and bananas'
4, 'only oranges'
5, 'empty'

Table basket_fruits
(id, basket, fruit, weight)
----------------
1, 1, 'apple', 2
2, 1, 'apple', 3
3, 1, 'orange', 2
4, 1, 'banana', 2
5, 2, 'apple', 2
6, 2, 'orange', 1
7, 3, 'apple', 2
8, 3, 'banana', 2
9, 4, 'orange', 2

SQL Fiddle with this data
我正在努力为这两种情况提出合理有效的查询：
我想取所有篮子，至少包含一个apple和至少一个orange，每一个都高于给定的重量。因此weight >= 2的预期结果是

1, 'apples, oranges and more'

对于weight >= 1来说

1, 'apples, oranges and more'
2, 'apples and small oranges'

我想把没有超过一定重量的水果的篮子都拿来。所以对于weight >= 2我希望

5, 'empty'

对于weight >= 3它应该返回

2, 'apples and small oranges'
3, 'apples and bananas'
4, 'only oranges'
5, 'empty'

权重约束只是“每个子关系必须满足某些约束”的占位符。在实践中，我们需要通过日期范围、状态等来限制子关系，但我不想让这个例子进一步复杂化。
（我正在使用postgresql，以防解决方案需要特定于数据库。）

最佳答案

我强烈建议为此使用group by和having。
对于第一个问题，此查询应该有效：

SELECT b.name
FROM baskets b INNER JOIN
     basket_fruits bf
     ON b.id = bf.basket
GROUP BY b.name
HAVING SUM( (bf.fruit = 'apple' AND bf.weight >= 2)::int ) > 0 AND
       SUM( (bf.fruit = 'orange' AND bf.weight >= 2)::int ) > 0 ;

第二个比较复杂，因为没有行。但是一个left join和coalesce()就足够了，所以你可以用同样的格式来表达它：

SELECT b.name
FROM baskets b LEFT JOIN
     basket_fruits bf
     ON b.id = bf.basket
GROUP BY b.name
HAVING SUM( (COALESCE(bf.weight, 0) >= 2)::int ) = 0

关于sql - 选择具有多个满足某些约束的子关系的所有行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50236381/