查询将所有内容连接到一行而不是单独的行 [英] 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屋!