本文介绍如何使用 SQL 查询具有多对多关系的表，并根据多个条件筛选结果。通过使用 GROUP BY 和 HAVING COUNT()，我们可以精确地找到满足所有指定条件的记录，避免使用多个查询和数组操作。文章提供了一个具体的示例，并展示了如何使用 SQL 实现高效的数据筛选。
在处理具有多对多关系的数据库时，经常需要根据多个条件筛选数据。例如，在一个菜谱（recipe）和食材（ingredient）的数据库中，我们可能需要找到包含 所有 指定食材的菜谱。本文将介绍如何使用 SQL 的 GROUP BY 和 HAVING COUNT() 子句来实现这一目标，避免使用多个查询和复杂的数组操作。
问题描述
假设我们有三张表：
- recipe (id, name) - 存储菜谱信息
- ingredient (id, name) - 存储食材信息
- recipe_ingredient (rid, iid) - 存储菜谱和食材之间的关系
我们的目标是编写一个 SQL 查询，该查询能够找到包含 所有 给定食材的菜谱。例如，如果我们想找到包含 "egg" 和 "milk" 的菜谱，查询应该返回只包含这两种食材的菜谱。
解决方案
我们可以使用以下 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 '%milk%' OR i.name LIKE '%egg%' GROUP BY r.id HAVING COUNT(DISTINCT i.id) = 2;
解释：
- JOIN: 首先，我们使用 JOIN 子句将三个表连接起来，以便我们可以访问菜谱、菜谱与食材的关系以及食材的信息。
- WHERE: WHERE 子句用于筛选出包含指定食材的记录。在这个例子中，我们使用 LIKE 运算符来匹配包含 "milk" 或 "egg" 的食材。注意这里使用了OR连接，目的是筛选出所有包含任一指定食材的菜谱。
- GROUP BY: GROUP BY r.id 子句将结果按照菜谱 ID 进行分组。这意味着我们将为每个菜谱创建一个组。
- HAVING COUNT(): HAVING COUNT(DISTINCT i.id) = 2 子句是关键。HAVING 子句用于筛选分组后的结果。COUNT(DISTINCT i.id) 计算每个组中不同食材的数量。= 2 确保我们只选择包含 所有 指定食材的菜谱。DISTINCT 关键字确保我们只计算不同的食材，即使一个菜谱多次使用同一种食材。
示例
假设我们有以下数据：
135topCMS! 网上订餐系统是根据目前流行的O2O模式为订餐行业量身定制的在线订餐软件。使用此系统可将线下商务的机会与互联网结合在了一起，让互联网成为线下交易的前台。这样线下服务就可以用线上来揽客，消费者可以用线上来筛选服务，还有成交可以在线结算，很快达到规模。该模式最重要的特点是：推广效果可查，每笔交易可跟踪。 135topCMS! 在线订餐平台系统是135topCMS! 团队根据多年互联
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 |
运行上面的查询将返回：
| id | name |
|---|---|
| 1 | pancakes |
因为只有 pancakes 包含了 eggs 和 milk。
注意事项
- LIKE 运算符用于模糊匹配。如果需要精确匹配，可以使用 = 运算符。
- COUNT(DISTINCT i.id) 确保我们只计算不同的食材。如果一个菜谱多次使用同一种食材，它只会被计算一次。
- HAVING 子句只能用于 GROUP BY 子句之后。
- WHERE 子句用于筛选单个记录，而 HAVING 子句用于筛选分组后的结果。
- 确保 WHERE 子句中的条件与 HAVING COUNT() 中的数量一致。
总结
使用 GROUP BY 和 HAVING COUNT() 子句可以有效地查询具有多对多关系的表，并根据多个条件筛选结果。这种方法避免了使用多个查询和复杂的数组操作，提高了查询效率和代码可读性。通过理解和掌握这种技术，可以更轻松地处理复杂的数据库查询任务。
