如何按其变体,variant_options及其来自不同表的sku对表进行分组

我的查询是:

SELECT p.id AS pId, p.name AS pName,
v.name AS vName,
vo.name AS voName,
s.sku, s.price, s.qty
FROM products p
LEFT JOIN variants v ON v.product_id = p.id
LEFT JOIN variant_options vo ON vo.variant_id = v.id
LEFT JOIN skus s ON s.product_id = p.id


它显示像这样

table:
pId | pName   | vName | voName | sku  | price | qty

1   | adidas  | size  | 42     | AD42 | 100   | 10
1   | adidas  | size  | 43     | AD42 | 100   | 10
1   | adidas  | size  | 42     | AD43 | 100   | 10
1   | adidas  | size  | 43     | AD43 | 100   | 10


我希望我的桌子变成:

table:
pId | pName   | vName | voName | sku  | price | qty

1   | adidas  | size  | 42     | AD42 | 100   | 10
                      | 43     | AD43 | 100   | 10

最佳答案

根据需要添加GROUP BY。

SELECT p.id AS pId, p.name AS pName,
v.name AS vName,
vo.name AS voName,
s.sku, s.price, s.qty
FROM products p
LEFT JOIN variants v ON v.product_id = p.id
LEFT JOIN variant_options vo ON vo.variant_id = v.id
LEFT JOIN skus s ON s.product_id = p.id
GROUP BY v.name, vo.name, s.product_id


但是有时您可能会选择只使用GROUP BY其中之一来减小粒度。例如:

GROUP BY s.product_id

10-07 19:27
查看更多