如何连接mysql中的两个表，数组中有匹配的数据和组

Question

我需要在mysql中连接两个表(产品和订单)与匹配的数据(订单id)和不同的数据(产品id)，应该在一个数组。

我尝试了一个简单的JOIN，但数据没有被分组。有人能给我点个火吗?

SELECT Order.id, Order.userId, Product.id AS productsIds
FROM Orders AS Order
JOIN Products AS Product
ON Order.id = Product.orderId

结果:

[
{
"id": 1,
"userId": 1,
"productsIds": 2
},
{
"id": 1,
"userId": 1,
"productsIds": 5
},
{
"id": 1,
"userId": 1,
"productsIds": 6
},
{
"id": 3,
"userId": 2,
"productsIds": 4
},
{
"id": 2,
"userId": 3,
"productsIds": 3
}
]

:

[
{
"id": 1,
"userId": 1,
"productsIds": [2, 5, 6]
},
{
"id": 3,
"userId": 2,
"productsIds": 4
},
{
"id": 2,
"userId": 3,
"productsIds": 3
}
]

Answer 1

您需要使用GROUP BY子句和GROUP_CONCAT在select语句中，将给出预期的结果。

下面是查询。

SELECT Order.id, Order.userId, GROUP_CONCAT(Product.id) AS productsIds FROM Orders AS Order JOIN Products AS Product ON Order.id = Product.orderId GROUP BY Order.id, Order.userId ;