多个MySQL表连接需要将一个表的结果合并到一个列字段中

I have this SQL query below which takes all my order records from a MySQL DB table and gets the order_id, order_number, and item_status column for each record in the nam_order_items DB table.

下面有一个SQL查询，它从MySQL DB表获取所有订单记录，并为nam_order_items DB表中的每个记录获取order_id、order_number和item_status列。

It then JOINS the order_id on the above table with the parent_id field on the sales_flat_order_address table in order to get the shipping address data for all the orders in my nam_order_items DB table.

然后，它与sales_flat_order_address表上的parent_id字段连接到上面表上的order_id，以便为我的nam_order_items DB表中的所有订单获取配送地址数据。

So far up to this point it all works as I need it to....

到目前为止,到目前为止一切顺利我需要....

SELECT sfo.order_id, sfo.order_number, sfo.item_status, shipping.city 
FROM nam_order_items AS sfo
JOIN sales_flat_order_address AS shipping ON shipping.parent_id = sfo.order_id
AND shipping.address_type =  'shipping'

Now to add on to this SQL I also need to JOIN and query another 3rd DB table to get all the related comments for an order record.

现在，要添加到这个SQL，我还需要连接并查询另一个第三个DB表，以获取订单记录的所有相关注释。

The order comments are stored in this DB table sales_flat_order_status_history under the comment column.

订单注释存储在这个DB表sales_flat_order_status_history中的注释列下。

So I add another JOIN to the above SQL so that is looks like below...

因此，我在上面的SQL中添加了另一个连接，如下所示。

SELECT sfo.order_id, sfo.order_number, sfo.item_status, shipping.city, comments.comment
FROM nam_order_items AS sfo
JOIN sales_flat_order_address AS shipping ON shipping.parent_id = sfo.order_id
AND shipping.address_type =  'shipping'
JOIN sales_flat_order_status_history AS comments ON comments.parent_id = sfo.order_id

Now this takes my original 6,000 records and turns it into 40,000 records. The reason is that each order record can have multiple comment records.

现在我把原来的6000张唱片变成了40000张。原因是每个订单记录可以有多个注释记录。

What I need to do though is make sure only my original 6,000 order records are returned in my result. I need to get all the comment records for each order from the sales_flat_order_status_history table and somehow concat them into 1 comments field on the result.

但是我需要做的是确保在结果中只返回我原来的6000个订单记录。我需要从sales_flat_order_status_history表中获取每个订单的所有评论记录，并以某种方式将它们转换为结果的一个评论字段。

So if an order has 1 comment record or 6 comment records, it will combine the 6 into 1 and show it on the order record.

因此，如果一个订单有1条评论记录或6条评论记录，它将把6合并为1，并在订单记录中显示出来。

Is this possible with just SQL and can someone help modify what I have if it is?

仅仅使用SQL就能做到这一点吗?如果是的话，有人能帮助我修改我的内容吗?

Working SQL from user LOCK's answer below...

使用SQL的用户锁定的答案如下…

SELECT sfo.order_id, sfo.order_number, sfo.item_status, shipping.street, shipping.city, shipping.region, shipping.country_id, comment.created_at, comment.comment
FROM nam_order_items AS sfo
JOIN sales_flat_order_address AS shipping ON shipping.parent_id = sfo.order_id
AND shipping.address_type =  'shipping'
JOIN
(
  select
    parent_id
    group_concat(comment order by parent_id asc separator ' | ') as comment
  from
    sales_flat_order_status_history
  group by
    parent_id
) comment ON 
  comment.parent_id = sfo.order_id

1 个解决方案

#1

Try something like this: group_concat(comments order by id asc separator ' ') as comment

尝试这样的方法:group_concat(由id asc分隔符的评论顺序)作为注释。

Note that order by id should be the id of the comment (so the comments are correctly ordered when concatenated). :

注意，按id排序应该是注释的id(因此在连接注释时，注释的顺序是正确的)。:

SELECT sfo.order_id, sfo.order_number, sfo.item_status, shipping.city, comments.comment
FROM nam_order_items AS sfo
JOIN sales_flat_order_address AS shipping ON shipping.parent_id = sfo.order_id
AND shipping.address_type =  'shipping'
JOIN
(
  select
    parent_id,
    group_concat(concat(created_at, ' - ', comment) order by entity_id asc separator ' ') as comment
  from
    sales_flat_order_status_history
  group by
    parent_id
) comments ON 
  comments.parent_id = sfo.order_id

#1