多个MySQL Table JOINS需要将1个表的结果合并到单个列字段中 [英] Multiple MySQL Table JOINS needs to concat the results of 1 table into a single column field

问题描述

下面有这个SQL查询，该查询从MySQL数据库表中获取所有订单记录，并为nam_order_items DB表中的每个记录获取order_id，order_number和item_status列.

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.

然后，它将上表中的order_id与sales_flat_order_address表中的parent_id字段联接在一起，以便获取我的nam_order_items DB表中所有订单的收货地址数据.

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.

到目前为止，一切都可以按我的需要进行....

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'

现在要添加到此SQL上，我还需要加入并查询另一个3rd DB表以获取所有相关的comments以获得订单记录.

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.

订单注释存储在此数据库表sales_flat_order_status_history中的comment列下.

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

所以我在上面的SQL中添加了另一个JOIN，因此如下所示...

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

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

现在，这将我的原始6,000条记录转换为40,000条记录.原因是每个订单记录可以有多个注释记录.

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.

我需要做的是确保结果中仅返回原始的6,000个订单记录.我需要从sales_flat_order_status_history表中获取每个订单的所有注释记录，并以某种方式将它们合并为结果的1个注释字段.

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.

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

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.

仅使用SQL就能做到吗，有人可以帮忙修改我拥有的内容吗?

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

从下面的用户LOCK答案中获取有效的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

推荐答案

尝试如下操作: group_concat(comments order by id asc separator ' ') as comment

请注意，order by id应该是注释的ID(因此，连接后注释的顺序正确). :

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

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

这篇关于多个MySQL Table JOINS需要将1个表的结果合并到单个列字段中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！