1. 首页
  2. 问题
  3. 多个MySQL Table JOINS需要将1个表的结果合并到单个列字段中
一尘不染

多个MySQL Table JOINS需要将1个表的结果合并到单个列字段中

sql

我有这样的SQL查询低于从MySQL数据库表通吃我的订单记录和获取order_id，order_number以及item_status列在每个记录nam_order_items数据库表。

然后，它将order_id上表中的与表中的parent_id字段连接起来sales_flat_order_address，以便获取我的nam_order_items数据库表中所有订单的收货地址数据。

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

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订单记录的所有相关信息。

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

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

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条记录。原因是每个订单记录可以有多个注释记录。

不过，我需要做的是确保结果中仅返回原始的6,000个订单记录。我需要从sales_flat_order_status_history表中获取每个订单的所有评论记录，并以某种方式将它们连接到结果的1个评论字段中。

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

仅使用SQL就能做到这一点吗？如果可以的话，有人可以帮助我修改吗？

从下面的用户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

阅读 199

收藏
2021-05-16

共1个答案

一尘不染

尝试这样的事情： group_concat(comments order by id asc separator ' ') as comment

请注意，它order by 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
2021-05-16