SQL：多对多关系和“ALL”子句

Question

我有一个表格 products 和一个表格 locations，它们通过表格 products_locations 在多对多关系中连接在一起。现在客户可以选择一组产品，我希望运行一个查询，只选择所有选定产品都可用的地点。

一开始似乎很简单，但我发现自己被如何实现这一点所困扰。我最初认为可以使用类似以下内容获取所有正确的位置 ID：

SELECT location_id
FROM products_locations
WHERE product_id = ALL [the user selected product ids]

但仔细一想，这似乎也没有意义（products_locations的结构非常简单，即[product_id, location_id]）。

如果您有任何关于如何构建这样一个查询的建议，将不胜感激。我觉得我可能忽视了一些基本的东西...

编辑：我正在使用mysql语法/方言。

快速示例：给定以下表格

| products   | | locations | | products_locations       |
| id | name  | | id | name | | product_id | location_id |
|------------| |-----------| |--------------------------|
| 1  | prod1 | | 1  | locA | | 1          | 2           |
| 2  | prod2 | | 2  | locB | | 2          | 1           |
| 3  | prod3 | |-----------| | 2          | 2           |
|------------|               | 3          | 1           |
                             |--------------------------|

如果用户选择产品1和2，则查询应该只返回位置2。如果用户选择产品2和3，则查询应该返回位置1。对于产品1、2和3，没有位置是有效的，对于产品2，两个位置都是有效的。

- Kugelblitz

---

你正在使用哪个数据库管理系统产品？"SQL"只是一种查询语言，而不是特定数据库产品的名称。请为您使用的数据库产品添加一个标签postgresql、oracle、sql-server、db2等。 - user330315

在开始之前，请添加一些示例表数据和预期结果。请查看https://stackoverflow.com/help/mcve。 - jarlh

我使用mysql作为方言，我已将其添加到原始问题中。我的错，我忘记指定。 - Kugelblitz

@jarlh 你说得对，我之前因为担心会让读者混淆而误删了一个例子，现在我已经将其简化并添加进去了。谢谢！ - Kugelblitz

Answer 1

我想出了一个查询，可以达到我需要的目的。虽然它不像我希望的那样简洁，但它似乎是我尝试查询所需的强大方法：

SELECT t.location_id
FROM (SELECT location_id, COUNT(*) as n_hits
      FROM products_locations
      WHERE product_id IN [the user selected products]
      GROUP BY location_id) t
WHERE n_hits = [the number of user selected products];

---

解释：

1. 我创建了一个临时表t，其中包含至少在用户选择中有一个匹配产品的每个location_id，以及该位置匹配用户选择中的产品的次数。这是通过按location_id分组来实现的。
2. 从临时表t中选择location_id，其中命中次数等于用户选择的产品数量。如果该数字较低，则知道至少有一个产品未与该位置匹配。

- Kugelblitz

---

以我自己的方式找到了解决这个问题的方法，然后发现了你的方法。也许这是唯一的解决方案，或者我们的想法是相同的。 - Gargamil