mysql：将表的列一对多连接起来

rdrgkggo 于 2021-06-20 发布在 Mysql

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

我有两张table( person + person_products )
每人至少有一种产品。
示例人员表：

person_id | name
------------------
1         | Alice
2         | Peter
3         | James

示例人员产品表：

id | person_id | description | price
------------------------------------
1  | 1         | iphone 5    | 100
2  | 1         | iphone 6    | 200
3  | 1         | samsung     | 300
4  | 2         | tv          | 110
5  | 3         | oven        | 250
6  | 3         | microwave   | 260

我想做以下工作：

SELECT p.person_id, 
some_concat_product_descriptions, 
some_concat_product_prices
FROM person p
LEFT JOIN person_products p on p.person_id = pp.person_id

预期结果：

person_id | concat_product_descriptions | concat_product_prices
---------------------------------------------------------------
1         | iphone 5, iphone 6, samsung | 100, 200, 300
2         | tv                          | 110
3         | oven, microwave             | 250, 260

我怎样才能做到这一点？

mysql concatenation

来源：https://stackoverflow.com/questions/52041385/mysql-concatenate-columns-of-tables-one-to-many

2条答案

按热度按时间

enxuqcxy1#

使用组\u concat

SELECT p.person_id, 
       group_concat(pp.description order by pp.description) as concat_product_descriptions,
       group_concat(pp.price order by pp.description) as concat_product_prices
FROM person p
LEFT JOIN person_products pp on p.person_id = pp.person_id
GROUP BY p.person_id

赞(0）回复(0）举报 2021-06-20

i2loujxw2#

使用组集合函数。完全不需要联接。

select 
  person_id,
  group_concat(description) separator ', ' as concat_product_descriptions,
  group_concat(price) separator ', ' as concat_product_prices
group by 
  person_id

赞(0）回复(0）举报 2021-06-20

我来回答

mysql：将表的列一对多连接起来

2条答案

相关问题

热门标签

最新问答