本文旨在提供一种高效的 SQL 查询方法，用于在具有多对多关系的表中，筛选出与另一张表中所有指定条件相关的记录。我们将通过一个食谱和配料的示例，详细讲解如何使用 GROUP BY 和 HAVING COUNT() 子句实现这一目标。
问题背景
假设我们有两个表：recipe（食谱）和 ingredient（配料），它们之间通过一个中间表 recipe_ingredient 建立多对多关系。我们需要查询出包含所有指定配料的食谱。例如，如果我们想找出既包含“鸡蛋”又包含“牛奶”的食谱，应该如何编写 SQL 语句？
数据表结构
为了更好地理解，我们先定义三个表的基本结构和示例数据：
recipe (食谱)
| id | name |
|---|---|
| 1 | pancakes |
| 2 | eggs |
ingredient (配料)
| id | name |
|---|---|
| 1 | eggs |
| 2 | flour |
| 3 | milk |
recipe_ingredient (食谱-配料关系)
| rid | iid |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
解决方案
以下 SQL 查询语句可以实现我们的目标：
SELECT r.id, r.name FROM recipe r JOIN recipe_ingredient ri ON r.id = ri.rid JOIN ingredient i ON i.id = ri.iid WHERE i.name LIKE '%ilk%' OR i.name LIKE '%eg%' GROUP BY r.id HAVING COUNT(DISTINCT i.id) = 2;
代码解释：
- SELECT r.id, r.name: 选择食谱的 ID 和名称。
- FROM recipe r JOIN recipe_ingredient ri ON r.id = ri.rid JOIN ingredient i ON i.id = ri.iid: 连接三个表，建立食谱和配料之间的关系。recipe 表通过 recipe_ingredient 表关联到 ingredient 表。
- WHERE i.name LIKE '%ilk%' OR i.name LIKE '%eg%': 筛选出配料名称中包含 "ilk" 或 "eg" 的记录。 这里可以根据实际需求，修改为包含其他配料的条件。
- GROUP BY r.id: 按照食谱 ID 进行分组。
- HAVING COUNT(DISTINCT i.id) = 2: HAVING 子句用于过滤分组后的结果。COUNT(DISTINCT i.id) 统计每个食谱包含的不同配料的数量。 = 2 表示只返回包含两个指定配料的食谱。这里数值需要与 WHERE 条件中筛选的配料数量对应。
执行结果：
| id | name |
|---|---|
| 1 | pancakes |
该查询结果表明，只有 id 为 1 的 pancakes 食谱同时包含 "ilk" (milk) 和 "eg" (egg) 这两种配料。
注意事项
- LIKE 模糊匹配: 示例中使用 LIKE 模糊匹配配料名称，可以根据实际情况调整为精确匹配（i.name = 'milk'）或更复杂的模式匹配。
- COUNT(DISTINCT i.id): 使用 DISTINCT 关键字可以避免重复计算相同的配料。
- WHERE 条件的数量: HAVING COUNT() 子句中的数值必须与 WHERE 条件中筛选的配料数量相对应，否则查询结果可能不正确。
- 性能优化: 对于大型数据集，可以考虑在相关字段上创建索引，以提高查询性能。
总结
通过结合 GROUP BY 和 HAVING COUNT() 子句，我们可以有效地在多对多关系的表中筛选出满足所有指定条件的记录。这种方法适用于各种需要同时满足多个关联条件的数据查询场景，例如：
- 查找同时购买了多个指定商品的客户
- 查找同时掌握多项特定技能的员工
- 查找包含所有指定关键词的文章
理解并掌握这种查询技巧，可以帮助我们更高效地从复杂的数据关系中提取所需信息。
