如何连接多个不是主键的行

xfyts7mz 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(326)

我有下列表格

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 |
+----------------+-----------+---------+

总结一下。这个
customer sam 有两套装备
outfit_id's 1 以及 2 . 装备 1 包含 items_id's , 2,3,4 . 装备 2 包含 items_id's , 1,2,3,5 . item_id's 是无用的数据位，但我需要它来保持主键的完整性。我真正关心的是 manufacturer_product_id . 如何选择所有选项 cus_id=2's 连在一起的一串 manufacturer_product_id's . 所以我的结果是：

+-----------+--------------------------+---------------------+
| 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 |
+-----------+--------------------------+---------------------+

你拿到了吗 manufacture_product_str 通过看 item_id's 装在一套衣服里（又名 items_in_outfit table。然后，从中，你找到每一个
item_id's manufacturer_product_id 并按照 manufacturer_product_id .
如何为客户的驾驶员识别码执行此操作？我的尝试没有意义，因为我所能做的就是将表连接在一起，但不知道如何获得 manufacturer_product_id's 以类似于上述输出的字符串形式

SELECT outfit_id, creation_date
FROM outfits
INNER JOIN items_in_outfit ON outfits.outfit_id = items_in_outfit.outfit_id
INNER JOIN customer ON outfits.cus_id = customer.cus_id
WHERE items customer.driver_id = 2463
ORDER BY outfits.creation_date

sql mysql concatenation Database select

来源：https://stackoverflow.com/questions/62986987/how-to-concatenate-multiple-rows-which-arent-the-primary-key

2条答案

按热度按时间

cyvaqqii1#

如果我理解正确，您需要一个聚合和 GROUP_CONCAT() 对于制造商ID：

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;

注意，我引入了表别名，因此查询更易于编写和读取。

赞(0）回复(0）举报 2021-07-24

nfg76nw02#

如果您需要多行（例如，多个服装标识），则需要一个 GROUP BY .

赞(0）回复(0）举报 2021-07-24