将多个子行合并为一行MYSQL [英] Combine Multiple child rows into one row 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;

这篇关于将多个子行合并为一行MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！