将多个子行组合成一行 MYSQL

提前谢谢，我就是不明白！

我有两张桌子

订购项目

ID | Item_Name
1  | Pizza
2  | Stromboli

有序 _ 选项

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

我希望输出的是一个 mysql 查询

输出

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

我已经尝试了大量的选项，大部分都以语法错误结尾，我也尝试了 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;

谢谢！ 乔

136859 次浏览
小开

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

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:

SET SESSION group_concat_max_len = 8192;
小开

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:

How to pivot a MySQL entity-attribute-value schema

小开

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);
小开

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
小开

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):

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.

小开

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 by for each intended item.

小开

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.value from the joint option table. Just left join the same option table multiple times in order to get all the options.

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.


提交答案