查询将所有内容连接到一行而不是单独的行 [英] Query concatenates everything into one row rather than into separate rows

问题描述

我有以下表格

Customer (driver_id is UNQIUE)
+--------+------+-----------+
| cus_id | name | driver_id |
+--------+------+-----------+
|      1 | bob  |      2342 |
|      2 | sam  |      2463 |
+--------+------+-----------+

Items (manufacture_product_id is UNIQUE) 
+---------+-------+-------------------------+
| item_id | name  | manufacturer_product_id |
+---------+-------+-------------------------+
|       1 | shirt |                    2131 |
|       2 | jeans |                     383 |
|       3 | pants |                       2 |
|       4 | watch |                   34634 |
|       5 | belt  |                       5 |
+---------+-------+-------------------------+

Outfits
+-----------+--------+---------------------+
| outfit_id | cus_id |    creation_date    |
+-----------+--------+---------------------+
|         1 |      2 | 2020-01-28 12:31:00 |
|         2 |      2 | 2020-01-29 15:23:12 |
+-----------+--------+---------------------+

items_in_outfit
+----------------+-----------+---------+
| outfit_item_id | outfit_id | item_id |
+----------------+-----------+---------+
|              1 |         1 |       2 |
|              2 |         1 |       3 |
|              3 |         1 |       5 |
|              4 |         2 |       1 |
|              5 |         2 |       2 |
|              5 |         2 |       3 |
|              6 |         2 |       5 |
+----------------+-----------+---------+

以上仅作总结。 顾客 sam 有两套服装，其中 outfit_id是 1 和 2 。服装 1 包含 items_id的， 2、3、4 。服装 2 包含 items_id的， 1,2,3,5 。

Just to summarize the above. The customer sam has 2 outfits with the outfit_id's 1 and 2. Outfit 1 contains the items_id's, 2,3,4. Outfit 2 contains the items_id's, 1,2,3,5.

item_id的是无用的数据位，但是我需要它来保持主键完整性。我真正关心的是 manufacturer_product_id 。如何在 manufacturer_product_id's 的串联字符串中选择所有 cus_id = 2的服装。所以我的输出是：

item_id's are useless bits of data but I need it to hold primary key integrity. What I really care about is the manufacturer_product_id. How do I select all of cus_id=2's outfits in a concatenated string of manufacturer_product_id's. So my output would be:

+-----------+--------------------------+---------------------+
| outfit_id | manufacturer_product_str |    creation_date    |
+-----------+--------------------------+---------------------+
|         1 | 2,383,34634              | 2020-01-28 12:31:00 |
|         2 | 2,5,383,2131             | 2020-01-29 15:23:12 |
+-----------+--------------------------+---------------------+

但是我得到的输出是

+-----------+--------------------------+---------------------+
| outfit_id | manufacturer_product_ids |    creation_date    |
+-----------+--------------------------+---------------------+
|         1 | 2,2,5,383,383,2131,34634 | 2020-01-28 12:31:00 |
+-----------+--------------------------+---------------------+

这实际上是将所有 outfit_id = 1 和 outfit_id = 2 一起。我希望每个服装编号都分开。

Which is essentially combining all of outfit_id = 1 and outfit_id = 2 together. I want each outfit_id to be separate.

SELECT o.outfit_id, o.creation_date,
       GROUP_CONCAT(i.manufacturer_product_id ORDER BY i.manufacturer_product_id) as manufacturer_product_ids
FROM outfits o INNER JOIN
     items_in_outfit io
     ON o.outfit_id = io.outfit_id JOIN
     items i
     ON io.item_id = i.item_id JOIN
     customer c
     ON o.cus_id = c.cus_id 
WHERE c.driver_id = 2463
ORDER BY o.creation_date;

推荐答案

您需要添加 group by 子句

 SELECT o.outfit_id, o.creation_date,
           GROUP_CONCAT(i.manufacturer_product_id ORDER BY i.manufacturer_product_id) as manufacturer_product_ids,
           GROUP_CONCAT(i.manufacturer_product_id ORDER BY i.manufacturer_product_id SEPARATOR ';') as manufacturer_product_ids_sep
    FROM outfits o INNER JOIN
         items_in_outfit io
         ON o.outfit_id = io.outfit_id JOIN
         items i
         ON io.item_id = i.item_id JOIN
         customer c
         ON o.cus_id = c.cus_id 
    WHERE c.driver_id = 2463
    group by o.outfit_id,o.creation_date
    ORDER BY o.creation_date

这篇关于查询将所有内容连接到一行而不是单独的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！