mysql - 跨多个表的复杂查询

Question

我正在简化我的实际案例，以便更快地理解要点，如果这看起来毫无意义，请注意。

我有一个名为 Candy_Central 的表。 Candy_Central 拥有我的糖果的所有 ID、每磅的价格以及所有 Candy 记录中相同的其他一些重要信息。它还有一个名为 Candy_Type 的字段。 Candy_Type 的值可能是巧克力、硬糖、软糖或液体。

我还有另一组用于糖果信息的表格，但它们的使用取决于糖果类型。例如，如果我想要有关我的优质巧克力棒的其余信息，我需要前往 Chocolate_Standard 获取其余信息。如果我要查找外面有糖的软糖蠕虫，我需要转到 Gummy_Standard。原因是一组信息的字段与另一组信息的字段完全不同。

我还有一层信息，颜色。我有蓝色的糖果，有黑色的糖果，还有两种颜色的糖果。我当前存储此信息的方式是，我有一个包含字段 Candy_Id 的表。和Color 。要查找所有颜色，我通常只需使用 color_id 字段对此表进行查询即可查找我感兴趣的糖果。但是 SQL 当然是查找所有结果。因此，如果我想找到所有的粘性信息，我想要进行某种连接或联合。我对这种类型的查询不是很熟悉，所以我不确定如何获取我想要的结果表，这基本上是[对伪查询感到抱歉](Candy_Main 中的项目作为 CM ... 项目来自 Gummy 作为 g ... 来自颜色的项目作为 c ... 其中 cm.candy_id = g.candy_id 和 cm.candy_id = c.candy_id )。如果软糖没有颜色，我只想为空。我可以为每个 candy_id 返回多行，但我希望尽可能限制这一点，以便后期处理(将多条糖果行变成我的应用程序中的一个对象)花费尽可能少的时间。

表格:

Candy_Main
    candy_id (primary key, auto increment, not null, etc)
    candy_name (varchar)
    candy_inventor_id (a primary key from some other table, but this is needed for the where clause.
    candy_type (varchar)

Gummy_Standard
    gummy_id (primary, auto)
    candy_id (should match to candy_main)
    softness (varchar)
    outer_coating_type (varchar)
    animal_likeness (varchar)

Gummy_Colors
    gummy_color_id (primary, auto)
    gummy_id (int)
    candy_id (int)
    color (varchar(64))

Gummy_Ingredients
    gummy_ingredient_id (primary, auto)
    gummy_id (int)
    candy_id (int)
    ingredient (varchar(64))

现在，我想查询所有条目 where candy_inventor_id = 5 AND candy_type =
'gummy' 。在查询结束时，我想要 candy_name, candy_type, softness, outer_coating_type, animal_likeness, colors, and ingredients .

颜色和成分是此查询中唯一不是 1 对 1 的对象，因此我希望条目数量与颜色和成分一样多。如果可能的话，感谢您的帮助。我有一种感觉，仅限制颜色或成分很容易，但同时限制两者就太困难了。

Answer 1

试试这个

SELECT c.candy_name, c.candy_type, s.softness, s.outer_coating_type, 
      s.animal_likeness, 
      GROUP_CONCAT(gc.color), 
      GROUP_CONCAT(gi.ingredient)
FROM candy_main c
LEFT JOIN gummy_standard s on s.candy_id=c.candy_id
LEFT JOIN gummy_colors gc on gc.candy_id = c.candy_id
LEFT JOIN gummy_ingredients gi cl on cl.candy_id = c.candy_id
WHERE c.candy_inventor_id=5 AND c.candy_type='gummy'
GROUP BY gc.color , gi.ingredient