mysql - 将多个子行合并为一行MYSQL

Question

在此先感谢，我似乎无法得到它!

我有两张 table

Ordered_Item

ID | Item_Name1  | Pizza2  | Stromboli

Ordered_Options

Ordered_Item_ID | Option_Number | Value        1               43         Pepperoni        1               44         Extra Cheese        2               44         Extra Cheese

What I am looking to output is a mysql query is something to this effect

Output

ID | Item_Name | Option_1 | Option_21    Pizza       Pepperoni  Extra Cheese2    Stromboli     NULL     Extra Cheese

I have tried numerous options most ending in syntax error, I have tried group_concat but thats not really what I am looking for. I have a crude example below of what I think might be a start. I need the options to be in the same order every time. And in the program where the info is collected there is no way to reliable ensure that will happen. Is it possible to have them concatenate according to option number. Also I know that I will never have over 5 options so a static solution would work

Select Ordered_Items.ID,
    Ordered_Items.Item_Name,
FROM Ordered_Items
    JOIN (SELECT Ordered_Options.Value FROM Ordered_Options Where Option_Number = 43) as Option_1 
        ON Ordered_Options.Ordered_Item_ID = Ordered_Item.ID
    JOIN (SELECT Ordered_Options.Value FROM Ordered_Options Where Option_Number = 44) as Option_2 
        ON Ordered_Options.Ordered_Item_ID = Ordered_Item.ID;

谢谢!乔

Answer 1

最简单的方法是在这里使用 GROUP_CONCAT 组函数..

select
  ordered_item.id as `Id`,
  ordered_item.Item_Name as `ItemName`,
  GROUP_CONCAT(Ordered_Options.Value) as `Options`
from
  ordered_item,
  ordered_options
where
  ordered_item.id=ordered_options.ordered_item_id
group by
  ordered_item.id

哪个会输出:

Id              ItemName       Options

1               Pizza          Pepperoni,Extra Cheese

2               Stromboli      Extra Cheese

这样您就可以拥有任意数量的选项，而无需修改您的查询。

啊，如果你看到你的结果被裁剪了，你可以像这样增加 GROUP_CONCAT 的大小限制:

SET SESSION group_concat_max_len = 8192;