sql - 通过要求所有满足条件的条件来过滤一对多查询

Question

设想以下表格:

创建表格框(id int, name text, ...);

创建表 thingsinboxes( id int, box_id int, thing enum('apple,'banana','orange');

表格看起来像:

Boxes:id | name1  | orangesOnly2  | orangesOnly23  | orangesBananas4  | miscthingsinboxes:id | box_id | thing1  |  1     | orange2  |  1     | orange3  |  2     | orange4  |  3     | orange5  |  3     | banana6  |  4     | orange7  |  4     | apple8  |  4     | banana

我如何选择至少包含一个橙子且没有非橙子的框？

假设我有几十万个盒子，盒子里可能有一百万个东西，这个规模如何？

如果可能的话，我想将所有这些都保留在 SQL 中，而不是使用脚本对结果集进行后处理。

我同时使用 postgres 和 mysql，所以子查询可能不好，因为 mysql 不优化子查询(无论如何，版本 6 之前)。

Answer 1

SELECT b.*
FROM boxes b JOIN thingsinboxes t ON (b.id = t.box_id)
GROUP BY b.id
HAVING COUNT(DISTINCT t.thing) = 1 AND SUM(t.thing = 'orange') > 0;

---

这是另一个不使用 GROUP BY 的解决方案:

SELECT DISTINCT b.*
FROM boxes b
  JOIN thingsinboxes t1 
    ON (b.id = t1.box_id AND t1.thing = 'orange')
  LEFT OUTER JOIN thingsinboxes t2 
    ON (b.id = t2.box_id AND t2.thing != 'orange')
WHERE t2.box_id IS NULL;

与往常一样，在对查询的可伸缩性或性能做出结论之前，您必须尝试使用真实的数据集，并衡量性能。