将多个子行合并为一行 MYSQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1067428/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Combine Multiple child rows into one row MYSQL
提问by Wouter van Nifterick
Thanks in advance, I just can't seem to get it!
在此先感谢，我似乎无法得到它！
I have two tables
我有两张桌子
Ordered_Item
Ordered_Item
ID | Item_Name 1 | Pizza 2 | Stromboli
Ordered_Options
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
我想要输出的是一个 mysql 查询就是这个效果
Output
输出
ID | Item_Name | Option_1 | Option_2 1 Pizza Pepperoni Extra Cheese 2 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
我尝试了许多以语法错误结尾的选项，我尝试过 group_concat 但这并不是我真正想要的。我在下面有一个粗略的例子，我认为这可能是一个开始。我需要选项每次都按相同的顺序排列。在收集信息的程序中，无法可靠地确保会发生。是否可以根据选项编号将它们连接起来。我也知道我永远不会有超过 5 个选项，所以静态解决方案会起作用
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;
Thanks! Joe
谢谢！乔
回答by Wouter van Nifterick
The easiest way would be to make use of the GROUP_CONCAT group function here..
最简单的方法是在这里使用 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
Which would output:
这将输出：
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.
这样您就可以拥有任意数量的选项，而无需修改您的查询。
Ah, if you see your results getting cropped, you can increase the size limit of GROUP_CONCAT like this:
啊，如果你看到你的结果被裁剪了，你可以像这样增加 GROUP_CONCAT 的大小限制：
SET SESSION group_concat_max_len = 8192;
回答by Wouter van Nifterick
I appreciate the help, I do think I have found a solution if someone would comment on the effectiveness I would appreciate it. Essentially what I did is. I realize it is somewhat static in its implementation but I does what I need it to do (forgive incorrect syntax)
我很感激帮助，如果有人对有效性发表评论，我认为我已经找到了解决方案，我将不胜感激。基本上我所做的是。我意识到它的实现有点静态，但我做了我需要做的事情（原谅不正确的语法）
SELECT
ordered_item.id as `Id`,
ordered_item.Item_Name as `ItemName`,
Options1.Value
Options2.Value
FROM ORDERED_ITEMS
LEFT JOIN (Ordered_Options as Options1)
ON (Options1.Ordered_Item.ID = Ordered_Options.Ordered_Item_ID
AND Options1.Option_Number = 43)
LEFT JOIN (Ordered_Options as Options2)
ON (Options2.Ordered_Item.ID = Ordered_Options.Ordered_Item_ID
AND Options2.Option_Number = 44);
回答by Wouter van Nifterick
If you really need multiple columns in your result, and the amount of options is limited, you can even do this:
如果您的结果中确实需要多列，并且选项数量有限，您甚至可以这样做：
select
ordered_item.id as `Id`,
ordered_item.Item_Name as `ItemName`,
if(ordered_options.id=1,Ordered_Options.Value,null) as `Option1`,
if(ordered_options.id=2,Ordered_Options.Value,null) as `Option2`,
if(ordered_options.id=43,Ordered_Options.Value,null) as `Option43`,
if(ordered_options.id=44,Ordered_Options.Value,null) as `Option44`,
GROUP_CONCAT(if(ordered_options.id not in (1,2,43,44),Ordered_Options.Value,null)) as `OtherOptions`
from
ordered_item,
ordered_options
where
ordered_item.id=ordered_options.ordered_item_id
group by
ordered_item.id
回答by Wouter van Nifterick
If you know you're going to have a limited number of max options then I would try this (example for max of 4 options per order):
如果您知道您将拥有有限数量的最大选项，那么我会尝试此操作（例如每个订单最多 4 个选项）：
Select OI.ID, OI.Item_Name, OO1.Value, OO2.Value, OO3.Value, OO4.Value
FROM Ordered_Items OI
LEFT JOIN Ordered_Options OO1 ON OO1.Ordered_Item_ID = OI.ID
LEFT JOIN Ordered_Options OO2 ON OO2.Ordered_Item_ID = OI.ID AND OO2.ID != OO1.ID
LEFT JOIN Ordered_Options OO3 ON OO3.Ordered_Item_ID = OI.ID AND OO3.ID != OO1.ID AND OO3.ID != OO2.ID
LEFT JOIN Ordered_Options OO4 ON OO4.Ordered_Item_ID = OI.ID AND OO4.ID != OO1.ID AND OO4.ID != OO2.ID AND OO4.ID != OO3.ID
GROUP BY OI.ID, OI.Item_Name
The group by condition gets rid of all of the duplicates that you would otherwise get. I've just implemented something similar on a site I'm working on where I knew I'd always have 1 or 2 matched in my child table, and I wanted to make sure I only had 1 row for each parent item.
按条件分组消除了否则您将获得的所有重复项。我刚刚在我正在工作的网站上实现了类似的东西，我知道我的子表中总是有 1 或 2 个匹配，我想确保每个父项只有 1 行。
回答by ck.tan
Here is how you would construct your query for this type of requirement.
以下是针对此类需求构建查询的方法。
select ID,Item_Name,max(Flavor) as Flavor,max(Extra_Cheese) as Extra_Cheese
from (select i.*,
case when o.Option_Number=43 then o.value else null end as Flavor,
case when o.Option_Number=44 then o.value else null end as Extra_Cheese
from Ordered_Item i,Ordered_Options o) a
group by ID,Item_Name;
You basically "case out" each column using case when, then select the max()for each of those columns using group byfor each intended item.
您基本上使用 来“排除”每一列case when，然后为每个预期项目max()使用group by的每一列选择。
回答by Vinko Vrsalovic
What you want is called a pivot, and it's not directly supported in MySQL, check this answer out for the options you've got:
您想要的称为枢轴，它在 MySQL 中不直接支持，请查看此答案以了解您拥有的选项：
回答by hailong
Joe Edel's answer to himself is actually the right approach to resolve the pivot problem.
乔埃德尔对自己的回答实际上是解决枢轴问题的正确方法。
Basically the idea is to list out the columns in the base table firstly, and then any number of options.valuefrom the joint option table. Just left jointhe same option table multiple times in order to get all the options.
基本上的想法是首先列出基表中的列，然后options.value是联合选项表中的任意数量。只需left join多次使用相同的选项表即可获得所有选项。
What needs to be done by the programming language is to build this query dynamically according to a list of options needs to be queried.
编程语言需要做的是根据需要查询的选项列表动态构建此查询。
