我有一个包含两个表的MySQL数据库:
表1:
Product_ID | Product_Name | Product_Description
------------------------------------------------
1 | Coca-Cola | Coke description.
2 | Pepsi | Pepsi description.
3 | Fanta | Fanta description
...
表2:
Product_ID | Product_SKU | Product_Size | Product_Price
------------------------------------------------
1 | COKE330 | 330ml | 0.79
1 | COKE500 | 500ml | 1.29
2 | PEPS330 | 330ml | 0.59
2 | PEPS500 | 500ml | 0.99
...
我想查询数据库并将这两个表连接在一起，但是合并具有相同产品id的重复行，这样返回的结果看起来就像:
Product_ID | Product_Name | Product_Sizes | Product_Prices | Product_Description
---------------------------------------------------------------------------------
1 | Coca-Cola | 330ml, 500ml | 0.79, 1.29 | Coke description.
2 | Pepsi | 330ml, 500ml | 0.59, 0.99 | Pepsi description.
...
事先谢谢你
您可以对行进行分组，并使用group_concat()来组装分组后的值，如:
select
a.product_id,
max(a.product_name),
group_concat(b.product_size) as product_sizes,
group_concat(b.product_price) as product_prices,
a.product_description
from table1 a
join table2 b on b.product_id = a.product_id
group by a.product_id
SELECT
A.product_id,
A.product_name,
CONCAT(A.product_size, ", ", B.product_size) as product_sizes,
CONCAT(A.product_price, ", ", B.product_price) as product_prices,
A.product_description
FROM table1 A, table2 B
WHERE A.product_id = B.product_id
