将多个子行合并为一行 MYSQL

2021-11-20 00:00:00 join select mysql

提前致谢，我似乎无法理解！

Thanks in advance, I just can't seem to get it!

我有两张桌子

Ordered_Item

ID | Item_Name
1  | Pizza
2  | Stromboli

Ordered_Options

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

我希望输出的是一个 mysql 查询，这就是这种效果

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

输出

ID | Item_Name | Option_1 | Option_2
1    Pizza       Pepperoni  Extra Cheese
2    Stromboli     NULL     Extra Cheese

我尝试了许多以语法错误结尾的选项，我尝试过 group_concat 但这并不是我真正想要的.我在下面有一个粗略的例子，我认为这可能是一个开始.我需要选项每次都按相同的顺序排列.在收集信息的程序中，没有办法可靠地确保会发生.是否可以根据选项编号将它们连接起来.我也知道我永远不会有超过 5 个选项，所以静态解决方案会起作用

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;

谢谢！乔

推荐答案

最简单的方法是在这里使用 GROUP_CONCAT 组功能..

The easiest way would be to make use of the GROUP_CONCAT group function here..

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

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

That way you can have as many options as you want without having to modify your query.

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

Ah, if you see your results getting cropped, you can increase the size limit of GROUP_CONCAT like this:

SET SESSION group_concat_max_len = 8192;

相关文章