Mysql多对多关系。过滤完全匹配

Question

你好。我想选择唯一具有准确选择的成分别名的菜肴？例如:成分别名表:

+----+----------+
| id |   Name   |
+----+----------+
| 22 |  potato  |
| 23 |  rice    |
| 29 |  chicken |
+----+----------+

成分表:

+------+-----------+----------+-------+------+----------+
|  id  |   name    | proteins | carbs | fats | alias_id |
+------+-----------+----------+-------+------+----------+
| 3043 | Chicken 1 | 44.0     | 3.0   | 3.0  |       29 |
| 3025 | Rice 1    | 44.0     | 32.0  | 23   |       23 |
| 3024 | Rice 2    | 23.0     | 22.0  | 33.0 |       23 |
| 3042 | Chicken 2 | 22.0     | 22.0  | 3.0  |       29 |
| 3022 | Potato 1  | 22.0     | 22.0  | 32.0 |       22 |
| 3021 | Potato 2  | 20.0     | 30.0  | 40.0 |       22 |
| 3041 | Chicken 3 | 11.0     | 11.0  | 11.0 |       29 |
| 3026 | Rice 3    | 1.0      | 1.0   | 1.0  |       23 |
| 3023 | Potato 3  | 1.0      | 2.0   | 3.0  |       22 |
+------+-----------+----------+-------+------+----------+

餐 table :

+----+-----------------------------------------+
| id |                  name                   |
+----+-----------------------------------------+
|  1 | Meal with Chicken 1 and Rice 1          |
|  2 | Meal with Chicken 1 and Rice 2          |
|  3 | Meal with Chicken 2 Potato 1            |
|  4 | Meal with Chicken 2 Potato 1 and Rice 1 |
+----+-----------------------------------------+

餐 table 餐点成分:

+-------+---------+---------------+--------+------+
|  id   | meal_id | ingredient_id | weight | role |
+-------+---------+---------------+--------+------+
| 13366 |       1 |          3043 |     13 |    1 |
| 13367 |       1 |          3025 |      1 |    1 |
| 13368 |       2 |          3043 |     12 |    2 |
| 13369 |       2 |          3024 |      8 |    3 |
| 13370 |       3 |          3042 |     22 |    1 |
| 13371 |       3 |          3022 |      1 |    1 |
| 13372 |       4 |          3042 |      3 |    1 |
| 13373 |       4 |          3022 |      3 |    3 |
| 13374 |       4 |          3024 |      2 |    2 |
+-------+---------+---------------+--------+------+

我如何仅得到含有仅成分且成分别名为土 bean 和鸡肉的餐点？在我的示例中，结果必须是 ID 为 3 的餐点 Meal with Chicken 2 Potato 1 ?

Answer 1

您需要连接所有 4 个表，按餐点分组并将条件放在 HAVING 子句中:

select m.id, m.name
from ingredient_alias ia
inner join ingredient i on i.alias_id = ia.id
inner join meal_ingredient mi on mi.ingredient_id = i.id
inner join meal m on m.id = mi.meal_id
group by m.id, m.name
having 
  count(distinct ia.name) = 2
  and 
  sum(ia.name not in ('potato', 'chicken')) = 0

参见 demo .
结果:

| id  | name                             |
| --- | -------------------------------- |
| 3   | Meal with Chicken 2 and Potato 1 |