mysql - 如何编写 SQL 查询以将两个表与映射表连接起来并获得每个结果只有一行的格式化输出？

Question

我有三个表。

---

Table: "Foods"
-----------
ID    Name
-----------
1     Apple
2     Strawberry
3     Celery
4     Peanuts
5     Toffee

---

Table: "Tags"
------------
ID     Name
------------
A      Sweet
B      Fruit
C      Crunchy

---

Table: "Mappings"
-------------------------
ID    TagID     FoodID
-------------------------
101   A          1
102   A          2
103   A          5
104   B          2   
105   C          1
106   C          3
107   C          4
108   C          5

我正在尝试运行查询并获得格式如下的结果:

** DESIRED RESULTS OUTPUT FORMAT **
------------------------------------------------------
ID    Name          IsSweet     IsFruit     IsCrunchy
------------------------------------------------------
1     Apple               1           1             1
2     Strawberry          1           1             0
3     Celery              0           0             1
4     Peanuts             0           0             1
5     Toffee              1           0             1

这是我目前正在使用的查询不起作用我希望如何:

SELECT foods.id, foods.name, 
  IF(tags.name = "Sweet", 1, 0) IsSweet, 
  IF(tags.name = "Fruit", 1, 0) IsFruit,
  IF(tags.name="Crunchy", 1, 0) IsCrunchy,
  from Foods, Tags, Mappings 
    where Foods.ID = Mappings.FoodID and Mappings.TagID = Tags.ID

我得到的结果如下所示，其中每个食品重复 N 次，每个存在的标签重复一次。

** ACTUAL RESULTS I'M GETTING **
------------------------------------------------------
ID    Name          IsSweet     IsFruit     IsCrunchy
------------------------------------------------------
1     Apple               1           0             0
1     Apple               0           1             0
1     Apple               0           0             1
2     Strawberry          1           0             0
2     Strawberry          0           1             0
3     Celery              0           0             1
4     Peanuts             0           0             1
5     Toffee              1           0             0
5     Toffee              0           0             1

如何编写查询以使结果格式化为我想要的格式？我已经尝试过外连接，并尝试使用 CASE/WHEN 语句或 GROUP BY 但我还没有弄明白。

有人可以给我一些指导吗？

Answer 1

应用最大值:

   SELECT foods.id, foods.name, 
        max(IF(tags.name = "Sweet", 1, 0)) IsSweet, 
        max(IF(tags.name = "Fruit", 1, 0)) IsFruit,
        max(IF(tags.name="Crunchy", 1, 0)) IsCrunchy
   from Foods, Tags, Mappings 
   where Foods.ID = Mappings.FoodID and Mappings.TagID = Tags.ID
   group by foods.id, foods.name